# -*-coding:utf-8 -*-
import random
import string
import time

import datetime
from operator import itemgetter

from flask import Flask, render_template, request, url_for, send_from_directory, make_response, session
import MySQLdb
import os
import json
from werkzeug.utils import secure_filename, redirect

# from docx import Document

import sys

reload(sys)
sys.setdefaultencoding('utf8')

app = Flask(__name__)

app.secret_key = '2stA0eZr98j/231dsdfHsH!jmN]LWX/,?RT'


@app.route('/logout')
def logout():
    session.pop('username', None)  # 登出
    return redirect('/')


@app.route('/', methods=['post', 'get'])
def index():
    state = 'f'
    nickname = ''
    uname = ''
    user_type = ''
    level = ''
    searchtext = '站内搜索'
    if 'username' in session:  # 判断用户是否登录，获取用户信息
        state = 't'
        nickname = session.get('nickname')
        user_type = session.get('user_type')
        uname = session.get('username')
        level = session.get('level')
    else:
        pass

    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()

    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 获取当前时间
    today = datetime.datetime.strptime(str(date), '%Y-%m-%d')
    delta = datetime.timedelta(days=1)
    yesr = today - delta
    yesrtoday = str(yesr)
    tomouse = time.strftime('%m', time.localtime(time.time()))  # 获取当前时间
    toyear = time.strftime('%Y', time.localtime(time.time()))  # 获取当前时间
    safedays = 1
    ydays = 365
    try:
        cursor.execute('select days from safedays where type = "1" order by id desc limit 1')
        d = cursor.fetchone()
        safedays = (today - datetime.datetime.strptime(d[0], '%Y-%m-%d')).days

        year = int(toyear)
        start = toyear + '-01-01'
        cdays = (today - datetime.datetime.strptime(start, '%Y-%m-%d')).days  # 已完成days天

        # 判断是否为闰年
        if (year % 4 == 0) & (year % 100 != 0):
            ydays = 366
        elif year % 400 == 0:
            ydays = 366
        else:
            ydays = 365

    except Exception:
        pass
    users = []
    list = []
    dq_list = []
    zh_list = []
    yx_list = []
    rl_list = []
    jc_list = []
    ja_list = []
    zx_list = []
    zj_list = []
    dd_list = []
    gt_list = []
    tpxw_list = []
    tzgg_list = []
    jcwt_list = []
    new = []
    picture = []
    path = []
    path2 = []
    username = request.values.get('username')
    password = request.values.get('password')

    try:
        cursor.execute("SELECT * FROM users")  # 从数据库取所有用户信息
        row1 = cursor.fetchall()
        for row in row1:
            users.append(row)

        cursor.execute("SELECT * FROM picture order by id DESC limit 5")  # 取首页图片
        rowp = cursor.fetchall()
        for row in rowp:
            picture.append(row)
    except Exception, e:
        pass

    for user in users:  # 用户登录功能，储存用户信息
        if username == user[0] and password == user[1]:
            session['id'] = user[0]
            session['username'] = user[0]
            session['u'] = user[0]
            session['password'] = password
            session['user_type'] = user[2]
            session['nickname'] = user[3]
            session['post'] = user[4]
            session['level'] = user[5]
            # if user[5] == "8":
            #     return redirect(url_for('mark_list',state = 'n'))
            return redirect('/')  # 登录成功返回首页
        else:
            pass

    try:
        cursor.execute("SELECT * FROM ja ORDER BY id DESC ")  # 取信息
    except Exception, e:
        pass
    rows = cursor.fetchall()
    for row in rows:
        list.append(row)

    for i in list:
        if new.__len__() < 16 and (i[6] == 'dq' or i[6] == 'zh' or i[6] == 'yx' \
                                           or i[6] == 'rl' or i[6] == 'jc' or i[6] == 'ja' or i[6] == 'zx' \
                                           or i[6] == 'zj' or i[6] == 'dd') and i[5] != 'tpxw':  # 获取8条中心部门最新发布的消息
            days = (today - datetime.datetime.strptime(str(i[4][0:10]), '%Y-%m-%d')).days  # 计算每条信息间隔天数，小于3天则有新消息提示
            # if i[8] == 't':
            if days < 2:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            new.append(i2)
        else:
            pass

        if i[5] == '1' and tzgg_list.__len__() < 8 and (i[6] == 'dq' or i[6] == 'zh' or i[6] == 'yx' \
                                                                or i[6] == 'rl' or i[6] == 'jc' or i[6] == 'ja' or
                                                                i[6] == 'zx' \
                                                                or i[6] == 'zj' or i[6] == 'dd'):  # “1”为通知公告；并且只取部室的信息
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days  # 计算每条信息与今天的间隔时间
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')  # 添加标记，标记为“t”则表示间隔天数小于3天，添加“new”标签
                else:
                    i2 = i + tuple('f')
                tzgg_list.append(i2)
        else:
            pass

        if i[6] == 'dq' and i[5] != 'tpxw' and dq_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(str(i[4][0:10]), '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                dq_list.append(i2)

        elif i[6] == 'zh' and i[5] != 'tpxw' and zh_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                zh_list.append(i2)
        elif i[6] == 'yx' and i[5] != 'tpxw' and yx_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                yx_list.append(i2)
        elif i[6] == 'rl' and i[5] != 'tpxw' and rl_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                rl_list.append(i2)
        elif i[6] == 'jc' and i[5] != 'tpxw' and jc_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                jc_list.append(i2)
        elif i[6] == 'ja' and i[5] != 'tpxw' and ja_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                ja_list.append(i2)
        elif i[6] == 'zx' and i[5] != 'tpxw' and zx_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                zx_list.append(i2)
        elif i[6] == 'zj' and i[5] != 'tpxw' and zj_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                zj_list.append(i2)
        elif i[6] == 'dd' and i[5] != 'tpxw' and dd_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] == 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                dd_list.append(i2)
        else:
            pass

        if i[5] == 'tpxw' and tpxw_list.__len__() < 5:
            p = i[9].split(';')
            path.append(p[0])
            tpxw_list.append(i)
        else:
            pass

        if i[5] == 'jcwt' and jcwt_list.__len__() < 8:
            p2 = i[9].split(';')
            path2.append(p2[0])
            jcwt_list.append(i)
        else:
            pass

    for i in list:
        # if new.__len__() < 16 and (i[6] == 'dq' or i[6] == 'zh' or i[6] == 'yx' \
        #                                   or i[6] == 'rl' or i[6] == 'jc' or i[6] == 'ja' or i[6] == 'zx' \
        #                                   or i[6] == 'zj' or i[6] == 'dd') and i[5] != 'tpxw':  # 获取8条中心部门最新发布的消息
        #     days = (today - datetime.datetime.strptime(str(i[4]), '%Y-%m-%d')).days  # 计算每条信息间隔天数，小于3天则有新消息提示
        #     if i[8] != 't':
        #         if days < 3:
        #             i2 = i + tuple('t')
        #         else:
        #             i2 = i + tuple('f')
        #         new.append(i2)
        # else:
        #     pass

        if i[5] == '1' and tzgg_list.__len__() < 8 and (i[6] == 'dq' or i[6] == 'zh' or i[6] == 'yx' \
                                                                or i[6] == 'rl' or i[6] == 'jc' or i[6] == 'ja' or i[
            6] == 'zx' \
                                                                or i[6] == 'zj' or i[6] == 'dd'):  # “1”为通知公告；并且只取部室的信息
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days  # 计算每条信息与今天的间隔时间
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')  # 添加标记，标记为“t”则表示间隔天数小于3天，添加“new”标签
                else:
                    i2 = i + tuple('f')
                tzgg_list.append(i2)
        else:
            pass

        if i[6] == 'dq' and i[5] != 'tpxw' and dq_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(str(i[4][0:10]), '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                dq_list.append(i2)

        elif i[6] == 'zh' and i[5] != 'tpxw' and zh_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                zh_list.append(i2)
        elif i[6] == 'yx' and i[5] != 'tpxw' and yx_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                yx_list.append(i2)
        elif i[6] == 'rl' and i[5] != 'tpxw' and rl_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                rl_list.append(i2)
        elif i[6] == 'jc' and i[5] != 'tpxw' and jc_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                jc_list.append(i2)
        elif i[6] == 'ja' and i[5] != 'tpxw' and ja_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                ja_list.append(i2)
        elif i[6] == 'zx' and i[5] != 'tpxw' and zx_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                zx_list.append(i2)
        elif i[6] == 'zj' and i[5] != 'tpxw' and zj_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                zj_list.append(i2)
        elif i[6] == 'dd' and i[5] != 'tpxw' and dd_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                dd_list.append(i2)
        elif i[6] == 'gt' and i[5] != 'tpxw' and gt_list.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if i[8] != 't':
                if days < 3:
                    i2 = i + tuple('t')
                else:
                    i2 = i + tuple('f')
                gt_list.append(i2)
        else:
            pass

    try:
        cursor.execute("SELECT * FROM charts where chart_type = '1' order by id desc limit 15")  # 取表一的数据
        charts = cursor.fetchall()
    except Exception, e:
        pass
    try:
        cursor.execute("SELECT * FROM charts where chart_type = '2' order by id desc limit 15")
        charts2 = cursor.fetchall()
    except Exception, e:
        pass

    try:
        cursor.execute("SELECT * FROM charts where chart_type = '3' order by id desc limit 15")
        charts3 = cursor.fetchall()
    except Exception, e:
        pass
    try:
        cursor.execute("SELECT * FROM charts where chart_type = '4' order by id desc limit 15")
        charts4 = cursor.fetchall()
    except Exception, e:
        pass
    try:
        cursor.execute("SELECT * FROM charts where chart_type = '5' order by id desc limit 15")
        charts5 = cursor.fetchall()
    except Exception, e:
        pass

    years = toyear + '%'  # 模糊查询
    jd1 = 0
    jd2 = 0
    cqjd1 = 0
    plan1 = 0
    plan2 = 0
    cqjd2 = 0
    try:
        cursor.execute("SELECT complete FROM charts where chart_type = '1' and date like '%s' " % years)
        complete1 = cursor.fetchall()
        cursor.execute(
            'select days from safedays where type = "2" and year = "%s" order by id desc limit 1' % toyear)  # 年度任务
        p1 = cursor.fetchone()
        cp1 = 0
        plan1 = p1[0]
        for c1 in complete1:
            cp1 = cp1 + int(c1[0])
        jd1 = round((float(cp1) / float(plan1)) * 100, 2)

        cqjd1 = round((float(cp1) - (float(plan1) * cdays) / ydays) / float(plan1) * 100, 2)

    except Exception, e:
        pass
    try:
        cursor.execute("SELECT complete FROM charts where chart_type = '2' and date like '%s' " % years)
        complete2 = cursor.fetchall()
        cursor.execute('select days from safedays where type = "3" order by id desc limit 1')
        p2 = cursor.fetchone()
        cp2 = 0
        plan2 = p2[0]
        for c2 in complete2:
            cp2 = cp2 + int(c2[0])
        jd2 = round((float(cp2) / float(plan2)) * 100, 2)
        cqjd2 = round((float(cp2) - (float(plan2) * cdays) / ydays) / float(plan2) * 100, 2)
    except Exception, e:
        pass

    slen = ''
    slist = []
    sign_type = []
    sign_list1 = []
    try:
        slen = cursor.execute(
            'select * from sign where username = "%s" and state = "n" order by id desc' % uname)  # 取该登录用户未签收的所有消息
        slist = cursor.fetchall()
        try:
            pass
            # for i in slist:
            #     cursor.execute('select * from ja where id = "%s" ' % i[1])  # 取每条信息中，需签收的部门信息
            #     info = cursor.fetchone()
            #     sign = info[7]
            #     sign_type = info[7].split(';')
            #     if i[2] in sign_type:  # 若该部门需签收且未签收，则添加到未签收列表中
            #         sign_list1.append(info)
            #     else:
            #         pass
            # slen = sign_list1.__len__()
            # session['sign_list'] = sign_list1
        except Exception:
            slen = 'error1'
    except Exception:
        slen = 'error2'
    handlenum = 0
    try:
        handlenum = cursor.execute(
            'select id from markhandle where markman = "%s" and marked = "n" ' % uname)  # 取该登录用户未签收的所有消息
    except Exception:
        pass

    unreadmail = 0
    try:
        unreadmail = cursor.execute(
            'select id from mail where receiver = "%s" and readed = "n" ' % uname)  # 取该登录用户未签收的所有消息
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return render_template('index.html', state=state, nickname=nickname, user_type=user_type, dq_list=dq_list,
                           zh_list=zh_list, gt_list=gt_list
                           , yx_list=yx_list, rl_list=rl_list, jc_list=jc_list, ja_list=ja_list, zx_list=zx_list,
                           zj_list=zj_list, dd_list=dd_list, tzgg_list=tzgg_list, tpxw_list=tpxw_list, new=new,
                           picture=picture, handlenum=handlenum,unreadmail=unreadmail,
                           charts=charts, charts2=charts2, charts3=charts3, charts4=charts4, charts5=charts5,
                           uname=uname, slen=slen, tomouse=tomouse,
                           safedays=safedays, yesrtoday=yesrtoday,
                           level=level, cp1=cp1, jd1=jd1, cp2=cp2, jd2=jd2, plan1=plan1, plan2=plan2, cqjd1=cqjd1,
                           cqjd2=cqjd2, path=path, path2=path2, jcwt_list=jcwt_list)


@app.route('/year_plan', methods=['get', 'post'])
def year_plan():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    user_type = session.get('user_type')
    year = ''
    income = ''
    ton = ''
    year = request.values.get('year')
    income = request.values.get('income')
    ton = request.values.get('ton')
    car = request.values.get('car')
    try:
        if (year.__len__() > 0 and income.__len__() > 0):
            try:
                cursor.execute('insert into safedays(days,type,year) value("%s","2","%s") ' % (income, year))
            except Exception:
                pass
        if (year.__len__() > 0 and ton.__len__() > 0):
            try:
                cursor.execute('insert into safedays(days,type,year) value("%s","3","%s") ' % (ton, year))
            except Exception:
                pass
        if (year.__len__() > 0 and car.__len__() > 0):
            try:
                cursor.execute('insert into safedays(days,type,year) value("%s","4","%s") ' % (car, year))
            except Exception:
                pass
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('year_plan.html', user_type=user_type)


@app.route('/safe_input', methods=['get', 'post'])
def safe_input():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    safedays = ''
    safedays = request.values.get('safedays')
    today = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    try:
        if safedays.__len__() > 0:
            cursor.execute('insert into safedays(days,type) value("%s","1") ' % safedays)
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('safe_input.html', today=today)


@app.route('/charts_input', methods=['post', 'get'])  # 表数据录入
def charts_input():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    complete1 = 0
    complete2 = 0
    complete3 = 0
    complete4 = 0
    complete5 = 0
    date = '2018-12-31'
    flag = 'i'
    date = request.values.get('date')

    try:
        today = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        toyear = time.strftime('%Y', time.localtime(time.time()))
        tomonth = time.strftime('%Y-%m', time.localtime(time.time()))
        year = int(toyear)
        stryear = toyear + '%'
        start = toyear + '-01-01'
        months = []
        for i in range(1, 10):
            strmonth = toyear + '-0' + str(i)
            months.append(strmonth)
        for i in range(10, 13):
            strmonth = toyear + '-' + str(i)
            months.append(strmonth)
        cdays = (
            datetime.datetime.strptime(date, '%Y-%m-%d') - datetime.datetime.strptime(start,
                                                                                      '%Y-%m-%d')).days  # 已完成days天
        pdays = 365
        # 判断是否为闰年
        if (year % 4 == 0) & (year % 100 != 0):
            pdays = 366 - cdays
        elif year % 400 == 0:
            pdays = 366 - cdays
        else:
            pdays = 365 - cdays
    except Exception:
        pass
    chart_name = ''
    user_type = session.get('user_type')
    session['months'] = months

    income = ''
    ton = ''
    car = ''

    income = request.values.get('income')
    ton = request.values.get('ton')
    car = request.values.get('car')
    perc = request.values.get('perc')
    hour = request.values.get('hour')
    list_date1 = []
    # list_date2 = []
    # list_date3 = []

    try:
        cursor.execute('select * from charts where chart_type = "1" and date like "%s" order by id desc' % stryear)
        chart1 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "2" and date like "%s" order by id desc' % stryear)
        chart2 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "3" and date like "%s" order by id desc ' % stryear)
        chart3 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "4" and date like "%s" order by id desc ' % stryear)
        chart4 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "5" and date like "%s" order by id desc ' % stryear)
        chart5 = cursor.fetchall()
        for c in chart1:
            complete1 = complete1 + int(c[2])
            list_date1.append(c[1])
        for c in chart2:
            complete2 = complete2 + int(c[2])
            # list_date2.append(c[1])
        for c in chart3:
            complete3 = complete3 + int(c[2])
            # list_date3.append(c[1])
    except:
        pass

    yplan1 = ''
    yplan2 = ''
    yplan3 = ''
    try:
        cursor.execute('select days from safedays where type = "2" and year = "%s" order by id desc limit 1' % toyear)
        yplan1 = cursor.fetchone()
        cursor.execute('select days from safedays where type = "3" and year = "%s" order by id desc limit 1' % toyear)
        yplan2 = cursor.fetchone()
        cursor.execute('select days from safedays where type = "4" and year = "%s" order by id desc limit 1' % toyear)
        yplan3 = cursor.fetchone()
    except Exception:
        pass
    try:
        plan1 = (int(yplan1[0]) - complete1) / pdays
        plan2 = (int(yplan2[0]) - complete2) / pdays
        plan3 = (int(yplan3[0]) - complete3) / pdays
    except Exception:
        pass

    # try:
    #     cursor.execute(
    #         'insert into charts(date,complete,plan,chart_type) value("%s","%s","%s","1")' % (
    #             date, income, plan1))
    #     cursor.execute(
    #         'insert into charts(date,complete,plan,chart_type) value("%s","%s","%s","2")' % (
    #             date, ton, plan2))
    #     cursor.execute(
    #         'insert into charts(date,complete,plan,chart_type) value("%s","%s","%s","3")' % (
    #             date, car, plan3))
    #     if date.__len__() > 0 and perc.__len__() > 0 and hour.__len__() > 0:
    #         cursor.execute(
    #             'insert into charts(date,complete,plan,chart_type) value("%s","%s","0","4")' % (
    #                 date, perc))
    #         cursor.execute(
    #             'insert into charts(date,complete,plan,chart_type) value("%s","%s","0","5")' % (
    #                 date, hour))
    #         conn.commit()
    # except Exception:
    #     pass

    try:
        if date.__len__() > 0 and income.__len__() > 0 and ton.__len__() > 0 and car.__len__() > 0 and perc.__len__() > 0 \
                and hour.__len__() > 0:
            if date in list_date1:
                try:
                    cursor.execute(
                        'update charts set complete = "%s",plan = "%s" where date = "%s" and chart_type = "1"' % (
                            income, plan1, date))
                    cursor.execute(
                        'update charts set complete = "%s",plan = "%s" where date = "%s" and chart_type = "2"' % (
                            ton, plan2, date))
                    cursor.execute(
                        'update charts set complete = "%s",plan = "%s" where date = "%s" and chart_type = "3"' % (
                            car, plan3, date))
                    cursor.execute(
                        'update charts set complete = "%s" where date = "%s" and chart_type = "4"' % (
                            perc, date))
                    cursor.execute(
                        'update charts set complete = "%s" where date = "%s" and chart_type = "5"' % (
                            hour, date))
                    conn.commit()
                    return redirect(url_for('charts_input'))
                except:
                    pass
            else:
                try:
                    cursor.execute(
                        'insert into charts(date,complete,plan,chart_type) value("%s","%s","%s","1")' % (
                            date, income, plan1))
                    cursor.execute(
                        'insert into charts(date,complete,plan,chart_type) value("%s","%s","%s","2")' % (
                            date, ton, plan2))
                    cursor.execute(
                        'insert into charts(date,complete,plan,chart_type) value("%s","%s","%s","3")' % (
                            date, car, plan3))
                    cursor.execute(
                        'insert into charts(date,complete,plan,chart_type) value("%s","%s","0","4")' % (
                            date, perc))
                    cursor.execute(
                        'insert into charts(date,complete,plan,chart_type) value("%s","%s","0","5")' % (
                            date, hour))
                    conn.commit()
                    return redirect(url_for('charts_input'))
                except Exception:
                    pass
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()

    return render_template('charts_input.html', tomonth=tomonth, months=months, flag=flag,
                           chart_name=chart_name, today=today, user_type=user_type, chart1=chart1, chart2=chart2,
                           chart3=chart3, chart4=chart4, chart5=chart5)


@app.route('/charts_plan', methods=['post', 'get'])
def charts_plan():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    income = ''
    ton = ''
    car = ''
    user_type = session.get('user_type')
    income = request.values.get('income')
    ton = request.values.get('ton')
    car = request.values.get('car')
    try:
        if income.__len__() > 0:
            try:
                cursor.execute('update charts set plan = "%s" where chart_type = "1"' % (income))
            except:
                pass
        if ton.__len__() > 0:
            try:
                cursor.execute('update charts set plan = "%s" where chart_type = "2"' % (ton))
            except:
                pass
        if car.__len__() > 0:
            try:
                cursor.execute('update charts set plan = "%s" where chart_type = "3"' % (car))
            except:
                pass
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return render_template('charts_plan.html', user_type=user_type)


@app.route('/charts_manage/<chart_type>', methods=['post', 'get'])  # 表数据管理
def charts_manage(chart_type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    chart_name = ''
    date = ''
    complete = ''
    plan = ''
    user_type = session.get('user_type')
    session['chart_type'] = chart_type
    if chart_type == '1':
        chart_name = '运输收入表'
    elif chart_type == '2':
        chart_name = '发送吨表'
    elif chart_type == '3':
        chart_name = '装车表'
    else:
        pass
    try:
        cursor.execute('select * from charts where chart_type = "%s" order by id desc' % chart_type)
        chart_list = cursor.fetchall()
    except Exception:
        pass
    return render_template('charts_manage.html', chart_name=chart_name, chart_list=chart_list, user_type=user_type)


@app.route('/charts_delete/<date>')  # 表数据删除
def charts_delete(date):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    chart_type = session.get('chart_type')
    try:
        cursor.execute('delete from charts where date = "%s"' % (date))
        conn.commit()
    except Exception, e:
        print e
    return redirect(url_for('charts_input'))
    # return redirect(url_for('charts_manage',chart_type=chart_type))


@app.route('/charts_edit/<date>', methods=['get', 'post'])  # 表数据修改
def charts_edit(date):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    row = []
    months = session.get('months')
    flag = 'e'
    chart_type = session.get('chart_type')
    toyear = time.strftime('%Y', time.localtime(time.time()))
    tomonth = time.strftime('%Y-%m', time.localtime(time.time()))
    stryear = toyear + '%'
    user_type = session.get('user_type')
    if chart_type == '1':
        chart_name = '运输收入表'
    elif chart_type == '2':
        chart_name = '发送吨表'
    elif chart_type == '3':
        chart_name = '装车表'
    else:
        pass
    try:
        cursor.execute('select * from charts where date = "%s" ' % date)
        row = cursor.fetchall()
    except Exception:
        pass
    try:
        cursor.execute('select * from charts where chart_type = "1" and date like "%s" order by id desc' % stryear)
        chart1 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "2" and date like "%s" order by id desc' % stryear)
        chart2 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "3" and date like "%s" order by id desc ' % stryear)
        chart3 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "4" and date like "%s" order by id desc ' % stryear)
        chart4 = cursor.fetchall()
        cursor.execute('select * from charts where chart_type = "5" and date like "%s" order by id desc ' % stryear)
        chart5 = cursor.fetchall()
    except Exception:
        pass
    return render_template('charts_input.html', months=months, tomonth=tomonth, flag=flag, row=row, user_type=user_type,
                           chart1=chart1, chart2=chart2, chart3=chart3, chart4=chart4, chart5=chart5)


# @app.route('/charts_edit/<id>', methods=['get', 'post']) #表数据修改
# def charts_edit(id):
#     conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
#     cursor = conn.cursor()
#     chart_type = session.get('chart_type')
#     session['chart_id']=id
#     user_type = session.get('user_type')
#     if chart_type == '1':
#         chart_name = '运输收入表'
#     elif chart_type == '2':
#         chart_name = '发送吨表'
#     elif chart_type == '3':
#         chart_name = '装车表'
#     else:
#         pass
#     try:
#         cursor.execute('select * from charts where id = "%s" ' % id)
#         row = cursor.fetchone()
#     except Exception:
#         pass
#     return render_template('charts_edit.html', row=row,chart_name=chart_name,user_type=user_type)

@app.route('/chartsedit_submit', methods=['get', 'post'])  # 表数据修改处理
def chartsedit_submit():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    chart_type = session.get('chart_type')
    id = session.get('chart_id')  # 取该表数据id
    try:
        if request.method == 'POST':
            date = request.values.get('date')
            complete = request.values.get('complete')
            plan = request.values.get('plan')
        else:
            pass

        if date.__len__() > 0 and complete.__len__() > 0 and plan.__len__() > 0:
            try:
                cursor.execute(
                    "update charts set date = '%s',complete='%s',plan='%s' where id = '%s'" % (
                        date, complete, plan, id))
            except Exception:
                pass
        else:
            pass
    except Exception, e:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return redirect(url_for('charts_manage', chart_type=chart_type))


@app.route('/search', methods=['post', 'get'])  # 站内搜索
def search():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    text = ''
    list = []
    try:
        text = request.values.get('text')  # 获取搜索文本
        if text.__len__() != 0:
            str = '%' + text + '%'  # 模糊查询
            cursor.execute('select * from ja where title like "%s" ORDER BY id DESC' % str)  # like模糊查询
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
    except Exception:
        pass
    return render_template('search.html', text=text, list=list)


@app.route('/bg_part', methods=['get', 'post'])
def bg_part():
    filename = ''
    try:
        basepath = os.path.dirname(__file__)
        part_type = request.values.get('part_type')
        bgimg = request.files['bgimg']

        if part_type == 'zh':
            filename = 'zhbg.jpg'
        elif part_type == 'ja':
            filename = 'jabg.jpg'
        elif part_type == 'yx':
            filename = 'yxbg.jpg'
        elif part_type == 'dd':
            filename = 'ddbg.jpg'
        elif part_type == 'zx':
            filename = 'zxbg.jpg'
        elif part_type == 'zj':
            filename = 'zjbg.jpg'
        elif part_type == 'rl':
            filename = 'rlbg.jpg'
        elif part_type == 'jc':
            filename = 'jcbg.jpg'
        elif part_type == 'dq':
            filename = 'dqbg.jpg'
        elif part_type == 'nnnyys':
            filename = 'nnnyysbg.jpg'
        elif part_type == 'ggyys':
            filename = 'ggyysbg.jpg'
        elif part_type == 'lbyys':
            filename = 'lbyysbg.jpg'
        elif part_type == 'czyys':
            filename = 'czyysbg.jpg'
        elif part_type == 'kys':
            filename = 'kysbg.jpg'
        elif part_type == 'nnnzgs':
            filename = 'nnnzgsbg.jpg'
        elif part_type == 'ggzgs':
            filename = 'ggzgsbg.jpg'
        elif part_type == 'ltzgs':
            filename = 'ltzgsbg.jpg'
        elif part_type == 'czzgs':
            filename = 'czzgsbg.jpg'
        else:
            pass
        upload_path = os.path.join(basepath, 'static/bgimg', filename)
        bgimg.save(upload_path)
    except Exception:
        pass
    return render_template('bg_part.html', filename=filename)


@app.route('/ja_index/<part_type>', methods=['get', 'post'])  # 各部门主页
def ja_index(part_type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    part_type1 = part_type
    partname = ''
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 获取当前时间
    today = datetime.datetime.strptime(str(date), '%Y-%m-%d')
    listname = part_type + '_list'
    if part_type == 'dq':
        partname = '党委办公室'  # 部门名称
        select_list = ['组织工作', '宣传工作', '纪检工作', '工会工作', '团青工作']  # 各部门导航条
        slen = select_list.__len__()
    elif part_type == 'zh':
        partname = '综合部'
        select_list = ['通知公告', '工作总结', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'yx':
        partname = '市场营销部'
        select_list = ['通知公告', '走访情况', '市场变化', '新增货源', '营销文电']
        slen = select_list.__len__()
    elif part_type == 'rl':
        partname = '人力资源部'
        select_list = ['通知公告', '人事管理', '工资定额', '规章制度', '社保年金']
        slen = select_list.__len__()
    elif part_type == 'jc':
        partname = '计划财务部'
        select_list = ['通知公告', '收入文电', '财务文电', '常用资料', '税务资料', '收入情况']
        slen = select_list.__len__()
    elif part_type == 'ja':
        partname = '技术安全部'
        select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
        slen = select_list.__len__()
    elif part_type == 'zx':
        partname = '装卸管理部'
        select_list = ['通知公告', '物资管理', '设备运用', '规章制度', '经验交流']
        slen = select_list.__len__()
    elif part_type == 'zj':
        partname = '职工教育部'
        select_list = ['通知公告', '职教文件', '培训考试', '学习计划', '岗位练兵', '培训课件', '职教台账']
        slen = select_list.__len__()
    elif part_type == 'dd':
        partname = '生产调度室'
        select_list = ['通知公告', '调度命令', '生产指标', '生产资料', '应急预案']
        slen = select_list.__len__()
    elif part_type == 'kys':
        partname = '快运所'
        select_list = ['通知公告', '经验交流', '工作信息', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggyys':
        partname = '贵港营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'lbyys':
        partname = '来宾营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czyys':
        partname = '崇左营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
        select_list = ['通知公告', '教育培训', '安全、生产', '规章制度', '设备动态', '职工风采', '党建工会']
        slen = select_list.__len__()
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czzgs':
        partname = '崇左装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    else:
        pass
    list = []
    new = []
    column1 = []
    column2 = []
    column3 = []
    column4 = []
    column5 = []
    column6 = []
    column7 = []
    column8 = []
    try:
        cursor.execute("SELECT * FROM ja WHERE part_type = '%s' ORDER BY id DESC " % (part_type1))
    except Exception, e:
        pass

    rows1 = cursor.fetchall()
    for row in rows1:
        list.append(row)
    for i in list:
        if new.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            new.append(i2)

        if i[5] == '1' and column1.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column1.append(i2)
        elif i[5] == '2' and column2.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column2.append(i2)
        elif i[5] == '3' and column3.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column3.append(i2)
        elif i[5] == '4' and column4.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column4.append(i2)
        elif i[5] == '5' and column5.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column5.append(i2)
        elif i[5] == '6' and column6.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column6.append(i2)
        elif i[5] == '7' and column7.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column7.append(i2)
        elif i[5] == '8' and column7.__len__() < 8:
            days = (today - datetime.datetime.strptime(i[4][0:10], '%Y-%m-%d')).days
            if days < 3:
                i2 = i + tuple('t')
            else:
                i2 = i + tuple('f')
            column8.append(i2)
        else:
            pass
    jcwt_list = []
    path = []
    try:
        cursor.execute('select * from ja where part_type = "%s" and type = "jcwt" order by id desc limit 4' % part_type)
        jcwt_list = cursor.fetchall()
        for i in jcwt_list:
            p = i[9].split(';')
            path.append(p[0])
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('ja_index.html', new=new, column1=column1, column2=column2, column3=column3, column4=column4,
                           column5=column5,
                           column6=column6, column7=column7, column8=column8, listname=listname, partname=partname,
                           part_type=part_type, jcwt_list=jcwt_list, path=path,
                           select_list=select_list, slen=slen)


@app.route('/part_output/<part_type>', methods=['post', 'get'])  # 各部门信息上传
def part_output(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')

    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    filename = ''
    filenames = ''
    filepaths = ''
    filename2 = ''
    filename3 = ''
    filename4 = ''
    partname = ''
    level = ''
    users = []
    select_list = []
    nickname = session.get('nickname')  # 获取登录人姓名
    level = session.get('level')
    user_type = session.get('user_type')  # 获取登录人权限
    part_type1 = user_type
    title = []
    content1=[]
    content = []
    sign_type = []
    sign_type = ''

    if part_type == 'dq':
        partname = '党委办公室'
        select_list = ['组织工作', '宣传工作', '纪检工作', '工会工作', '团青工作']
        slen = select_list.__len__()
    elif part_type == 'zh':
        partname = '综合部'
        select_list = ['通知公告', '工作总结', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'yx':
        partname = '市场营销部'
        select_list = ['通知公告', '走访情况', '市场变化', '新增货源', '营销文电']
        slen = select_list.__len__()
    elif part_type == 'rl':
        partname = '人力资源部'
        select_list = ['通知公告', '人事管理', '工资定额', '规章制度', '社保年金']
        slen = select_list.__len__()
    elif part_type == 'jc':
        partname = '计划财务部'
        select_list = ['通知公告', '收入文电', '财务文电', '常用资料', '税务资料', '收入情况']
        slen = select_list.__len__()
    elif part_type == 'ja':
        partname = '技术安全部'
        select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
        slen = select_list.__len__()
    elif part_type == 'zx':
        partname = '装卸管理部'
        select_list = ['通知公告', '物资管理', '设备运用', '规章制度', '经验交流']
        slen = select_list.__len__()
    elif part_type == 'zj':
        partname = '职工教育部'
        select_list = ['通知公告', '职教文件', '培训考试', '学习计划', '岗位练兵', '培训课件', '职教台账']
        slen = select_list.__len__()
    elif part_type == 'dd':
        partname = '生产调度室'
        select_list = ['通知公告', '调度命令', '生产指标', '生产资料', '应急预案']
        slen = select_list.__len__()
    elif part_type == 'kys':
        partname = '快运所'
        select_list = ['通知公告', '经验交流', '工作信息', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggyys':
        partname = '贵港营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'lbyys':
        partname = '来宾营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czyys':
        partname = '崇左营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
        select_list = ['通知公告', '教育培训', '安全、生产', '规章制度', '设备动态', '职工风采', '党建工会']
        slen = select_list.__len__()
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czzgs':
        partname = '崇左装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    else:
        slen = 0

    try:
        if request.method == 'POST':
            basepath = os.path.dirname(__file__)
            title = request.values.get('title')
            content1 = request.values.get('content')
            content = MySQLdb.escape_string(content1)
            text_tpye = request.values.get('text_type')
            sign_types = request.values.getlist('sign_type')
            approver = request.values.get('approver')
            publisher = request.values.get('publisher')
            number = request.values.get('number')
            for i in sign_types:
                sign_type = sign_type + i + ";"

            filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt2 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt3 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt4 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt1)
            rnd2 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt2)
            rnd3 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt3)
            rnd4 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt4)

            try:
                file1 = request.files['file1']
                filename = file1.filename
                if filename.__len__() > 0:
                    fname, fext = os.path.splitext(filename)
                    rnd_name1 = '%s%s' % (rnd1, fext)
                    upload_path1 = os.path.join(basepath, 'static\uploads', rnd_name1)
                    file1.save(upload_path1)
                    filepaths = rnd_name1
                    filenames = filename
            except Exception:
                pass

            try:
                file2 = request.files['file2']
                filename2 = file2.filename
                if filename2.__len__() > 0:
                    fname, fext = os.path.splitext(filename2)
                    rnd_name2 = '%s%s' % (rnd2, fext)
                    upload_path2 = os.path.join(basepath, 'static\uploads', rnd_name2)
                    file2.save(upload_path2)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name2
                        filenames = filenames + filename2
                    else:
                        filepaths = filepaths + ';' + rnd_name2
                        filenames = filenames + ';' + filename2


            except Exception:
                pass

            try:
                file3 = request.files['file3']
                filename3 = file3.filename
                if filename3.__len__() > 0:
                    fname, fext = os.path.splitext(filename3)
                    rnd_name3 = '%s%s' % (rnd3, fext)
                    upload_path3 = os.path.join(basepath, 'static\uploads', rnd_name3)
                    file3.save(upload_path3)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name3
                        filenames = filenames + filename3
                    else:
                        filepaths = filepaths + ';' + rnd_name3
                        filenames = filenames + ';' + filename3


            except Exception:
                pass

            try:
                file4 = request.files['file4']
                filename4 = file4.filename
                if filename4.__len__() > 0:
                    fname, fext = os.path.splitext(filename4)
                    rnd_name4 = '%s%s' % (rnd4, fext)
                    upload_path4 = os.path.join(basepath, 'static\uploads', rnd_name4)
                    file4.save(upload_path4)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name4
                        filenames = filenames + filename4
                    else:
                        filepaths = filepaths + ';' + rnd_name4
                        filenames = filenames + ';' + filename4

            except Exception:
                pass
    except Exception, e:
        pass

    if title.__len__() > 0:
        try:
            cursor.execute(
                "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number) value('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    title, content, filenames, date, text_tpye, part_type1, sign_type, filepaths, publisher,approver,number))
        except Exception:
            cursor.execute(
                "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number) value('%s','%s','','%s','%s','%s','%s','','%s','%s','%s')" % (
                    title, content, date, text_tpye, part_type1, sign_type, publisher,approver,number))
    else:
        pass

    try:
        cursor.execute('select id from ja order by id desc limit 1')  # 获取最新上传信息的id
        jid = cursor.fetchone()
        ulen = cursor.execute('select username,nickname,user_type,level from users')
        us = cursor.fetchall()
        n = cursor.execute('select id from sign where id = "%s" ' % (jid[0]))  # 判断该消息是否录入签收表
        if n == 0:
            if level == '3':
                for i in range(0, ulen):
                    if us[i][0] in sign_types and (us[i][3] == '3' or us[i][3] == '4'):
                        cursor.execute(
                            'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                                jid[0], us[i][0], us[i][1], us[i][2]))
            elif level == '4':
                for i in range(0, ulen):
                    if us[i][0] in sign_types and us[i][3] == '5':
                        cursor.execute(
                            'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                                jid[0], us[i][0], us[i][1], us[i][2]))
            conn.commit()
            return redirect(url_for('part_manage', part_type=user_type))
        else:
            pass
    except Exception:
        pass
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('part_output.html', sign_type=sign_type, jid=jid, user_type=user_type, partname=partname,
                           nickname=nickname, select_list=select_list, slen=slen, part_type=part_type, level=level,
                           users=users)


@app.route('/output/<part_type>', methods=['post', 'get'])
def output(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    filename = ''
    filename2 = ''
    filename3 = ''
    filename4 = ''
    filenames = ''
    partname = ''
    top = 'n'
    part_type1 = part_type
    slen = ''
    title = []
    content = []
    select_list = []
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    level = session.get('level')
    if user_type == part_type:
        pass
    elif user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('permit'))
    if part_type == 'dq':
        partname = '党委办公室'
        select_list = ['组织工作', '宣传工作', '纪检工作', '工会工作', '团青工作']
        slen = select_list.__len__()
    elif part_type == 'zh':
        partname = '综合部'
        select_list = ['通知公告', '工作总结', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'yx':
        partname = '市场营销部'
        select_list = ['通知公告', '走访情况', '市场变化', '新增货源', '营销文电']
        slen = select_list.__len__()
    elif part_type == 'rl':
        partname = '人力资源部'
        select_list = ['通知公告', '人事管理', '工资定额', '规章制度', '社保年金']
        slen = select_list.__len__()
    elif part_type == 'jc':
        partname = '计划财务部'
        select_list = ['通知公告', '收入文电', '财务文电', '常用资料', '税务资料', '收入情况']
        slen = select_list.__len__()
    elif part_type == 'ja':
        partname = '技术安全部'
        select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
        slen = select_list.__len__()
    elif part_type == 'zx':
        partname = '装卸管理部'
        select_list = ['通知公告', '物资管理', '设备运用', '规章制度', '经验交流']
        slen = select_list.__len__()
    elif part_type == 'zj':
        partname = '职工教育部'
        select_list = ['通知公告', '职教文件', '培训考试', '学习计划', '岗位练兵', '培训课件', '职教台账']
        slen = select_list.__len__()
    elif part_type == 'dd':
        partname = '生产调度室'
        select_list = ['通知公告', '调度命令', '生产指标', '生产资料', '应急预案']
        slen = select_list.__len__()
    elif part_type == 'tpxw':
        partname = '图片新闻'
        select_list = []
        slen = select_list.__len__()
    else:
        pass
    sign_type = ''
    try:
        if request.method == 'POST':
            basepath = os.path.dirname(__file__)
            title = request.values.get('title')
            content = request.values.get('content')
            text_tpye = request.values.get('text_type')
            sign_types = request.values.getlist('sign_type')
            approver = request.values.get('approver')
            publisher = request.values.get('publisher')
            number = request.values.get('number')
            top = request.values.get('top')
            for i in sign_types:
                sign_type = sign_type + i + ";"

            filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt2 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt3 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt4 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt1)
            rnd2 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt2)
            rnd3 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt3)
            rnd4 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt4)

            try:
                file1 = request.files['file1']
                filename = file1.filename
                if filename.__len__() > 0:
                    fname, fext = os.path.splitext(filename)
                    rnd_name1 = '%s%s' % (rnd1, fext)
                    upload_path1 = os.path.join(basepath, 'static\uploads', rnd_name1)
                    file1.save(upload_path1)
                    filepaths = rnd_name1
                    filenames = filename


            except Exception:
                pass

            try:
                file2 = request.files['file2']
                filename2 = file2.filename
                if filename2.__len__() > 0:
                    fname, fext = os.path.splitext(filename2)
                    rnd_name2 = '%s%s' % (rnd2, fext)
                    upload_path2 = os.path.join(basepath, 'static\uploads', rnd_name2)
                    file2.save(upload_path2)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name2
                        filenames = filenames + filename2
                    else:
                        filepaths = filepaths + ';' + rnd_name2
                        filenames = filenames + ';' + filename2


            except Exception:
                pass

            try:
                file3 = request.files['file3']
                filename3 = file3.filename
                if filename3.__len__() > 0:
                    fname, fext = os.path.splitext(filename3)
                    rnd_name3 = '%s%s' % (rnd3, fext)
                    upload_path3 = os.path.join(basepath, 'static\uploads', rnd_name3)
                    file3.save(upload_path3)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name3
                        filenames = filenames + filename3
                    else:
                        filepaths = filepaths + ';' + rnd_name3
                        filenames = filenames + ';' + filename3


            except Exception:
                pass

            try:
                file4 = request.files['file4']
                filename4 = file4.filename
                if filename4.__len__() > 0:
                    fname, fext = os.path.splitext(filename4)
                    rnd_name4 = '%s%s' % (rnd4, fext)
                    upload_path4 = os.path.join(basepath, 'static\uploads', rnd_name4)
                    file4.save(upload_path4)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name4
                        filenames = filenames + filename4
                    else:
                        filepaths = filepaths + ';' + rnd_name4
                        filenames = filenames + ';' + filename4

            except Exception:
                pass


    except Exception, e:
        pass
    if title.__len__() > 0:
        try:
            cursor.execute(
                "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number,top) value('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    title, content, filenames, date, text_tpye, part_type1, sign_type, filepaths, publisher,approver,number,top))
        except Exception:
            cursor.execute(
                "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number,top) value('%s','%s','','%s','%s','%s','%s','','%s','%s','%s','%s')" % (
                    title, content, date, text_tpye, part_type1, sign_type, publisher,approver,number,top))
    else:
        pass

    conn.commit()

    try:
        cursor.execute('select id from ja order by id desc limit 1')
        jid = cursor.fetchone()
        ulen = cursor.execute('select username,nickname,user_type,level from users')
        us = cursor.fetchall()
        n = cursor.execute('select id from sign where id = "%s" ' % (jid[0]))
        if n == 0:
            for i in range(0, ulen):
                if us[i][0] in sign_types and (us[i][3] == '3' or us[i][3] == '4'):
                    cursor.execute(
                        'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                            jid[0], us[i][0], us[i][1], us[i][2]))
        else:
            pass
    except Exception:
        pass
    users = []
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('output.html', user_type=user_type, part_type=part_type, partname=partname,
                           nickname=nickname, users=users,
                           select_list=select_list, slen=slen)


@app.route('/part_manage/<part_type>', methods=['get', 'post'])
def part_manage(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    level = session.get('level')
    if user_type == part_type:
        pass
    elif user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('permit'))

    if part_type == 'dq':
        partname = '党委办公室'
    elif part_type == 'zh':
        partname = '综合部'
    elif part_type == 'yx':
        partname = '市场营销部'
    elif part_type == 'rl':
        partname = '人力资源部'
    elif part_type == 'jc':
        partname = '计划财务部'
    elif part_type == 'ja':
        partname = '技术安全部'
    elif part_type == 'zx':
        partname = '装卸管理部'
    elif part_type == 'zj':
        partname = '职工教育部'
    elif part_type == 'dd':
        partname = '生产调度室'
    elif part_type == 'kys':
        partname = '快运所'
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
    elif part_type == 'ggyys':
        partname = '贵港营业所'
    elif part_type == 'lbyys':
        partname = '来宾营业所'
    elif part_type == 'czyys':
        partname = '崇左营业所'
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
    elif part_type == 'czzgs':
        partname = '崇左装管所'
    else:
        pass

    try:
        if part_type == 'ja':
            cursor.execute(
                'select * from ja WHERE part_type ="%s" or part_type = "tpxw" ORDER BY id DESC ' % (part_type))
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
        else:
            cursor.execute('select * from ja WHERE part_type ="%s" ORDER BY id DESC ' % (part_type))
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
    except Exception, e:
        pass
    return render_template('part_manage.html', list=list, partname=partname, user_type=user_type, nickname=nickname,
                           part_type=part_type, level=level)


@app.route('/delete_list/<part_type>', methods=['get', 'post'])
def delete_list(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    session['part_type'] = part_type

    if user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('part_manage', part_type=user_type))

    if part_type == 'dq':
        partname = '党委办公室'
    elif part_type == 'zh':
        partname = '综合部'
    elif part_type == 'yx':
        partname = '市场营销部'
    elif part_type == 'rl':
        partname = '人力资源部'
    elif part_type == 'jc':
        partname = '计划财务部'
    elif part_type == 'ja':
        partname = '技术安全部'
    elif part_type == 'zx':
        partname = '装卸管理部'
    elif part_type == 'zj':
        partname = '职工教育部'
    elif part_type == 'dd':
        partname = '生产调度室'
    else:
        pass

    try:
        cursor.execute('select * from ja WHERE part_type ="%s" ORDER BY id DESC ' % (part_type))
        rows = cursor.fetchall()
        for row in rows:
            if row[8] == 't':
                list.append(row)

        for row in rows:
            if row[8] != 't':
                list.append(row)
                # cursor.execute('select part_type from ja where id = "17"')
                # row = cursor.fetchone()
                # name = row[0]
    except Exception, e:
        pass

    return render_template('delete_list.html', list=list, partname=partname, nickname=nickname, user_type=user_type)


@app.route('/report/<part_type>', methods=['get', 'post'])
def report(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    list = []
    partname = ''
    read = 'n'
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == part_type:
        pass
    elif user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('permit'))

    if part_type == 'kys':
        partname = '快运所'
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
    elif part_type == 'ggyys':
        partname = '贵港营业所'
    elif part_type == 'lbyys':
        partname = '来宾营业所'
    elif part_type == 'czyys':
        partname = '崇左营业所'
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
    elif part_type == 'czzgs':
        partname = '崇左装管所'
    else:
        pass
    report_type = ''
    content1 = ''
    title = ''
    report_types = []

    try:
        ulen = cursor.execute('select username,user_type,nickname from users')
        us = cursor.fetchall()
    except Exception:
        pass
    try:
        if request.method == 'POST':
            basepath = os.path.dirname(__file__)
            title = request.values.get('title')
            content1 = request.values.get('content')
            content = MySQLdb.escape_string(content1)
            report_types = request.values.getlist('report_type')
            for i in report_types:
                report_type = report_type + i + ";"

            filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt1)

            try:
                file1 = request.files['file']
                filename = file1.filename
                if filename.__len__() > 0:
                    fname, fext = os.path.splitext(filename)
                    rnd_name1 = '%s%s' % (rnd1, fext)
                    upload_path = os.path.join(basepath, 'static\uploads', rnd_name1)
                    file1.save(upload_path)
            except Exception:
                pass

    except Exception, e:
        pass
    if title.__len__() > 0:
        try:
            for u in us:
                if u[0] in report_types:
                    cursor.execute(
                        "insert into report(title,content,filename,filepath,report_type,partname,part_type,time,readed,nickname,username,uname,see) value('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','y')" % (
                            title, content, filename, rnd_name1, report_type, partname, part_type, date, read, nickname,
                            u[0], u[2]))
        except Exception:
            for u in us:
                if u[0] in report_types:
                    cursor.execute(
                        "insert into report(title,content,report_type,partname,part_type,time,readed,nickname,username,uname,see) value('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','y')" % (
                            title, content, report_type, partname, part_type, date, read, nickname, u[0], u[2]))
    else:
        pass
    users = []
    try:
        cursor.execute('select * from users  order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return render_template('report.html', user_type=user_type, part_type=part_type, nickname=nickname, users=users)


@app.route('/reported_list/<part_type>')
def reported_list(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    username = session.get('username')
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == part_type:
        pass
    elif user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('permit'))
    list = []
    try:
        cursor.execute('select * from report where part_type = "%s" ORDER BY id DESC ' % part_type)
        rows = cursor.fetchall()
        # for row in rows:
        #     report_type = row[5].split(';')
        #     if part_type in report_type:
        #         list.append(row)
        list = rows
    except Exception, e:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('reported_list.html', list=list, part_type=part_type, user_type=user_type, nickname=nickname)


@app.route('/report_list/<part_type>')
def report_list(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    username = session.get('username')
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    level = session.get('level')
    if user_type == part_type:
        pass
    elif user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('permit'))

    report_type = ''
    list = []
    try:
        cursor.execute('select * from report where username = "%s" and see = "y" ORDER BY id DESC ' % username)
        rows = cursor.fetchall()
        # for row in rows:
        #     report_type = row[5].split(';')
        #     if part_type in report_type:
        #         list.append(row)
    except Exception, e:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('report_list.html', list=rows, user_type=user_type, part_type=part_type, nickname=nickname,
                           level=level)


@app.route('/report_display/<id>')
def report_display(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    level = session.get('level')
    part_type = user_type
    row = []
    level = ''
    level = session.get('level')

    try:
        cursor.execute('select * from report where id = "%s"' % (id))
        row = cursor.fetchone()
    except Exception, e:
        pass
    try:  # cursor.execute('update ja set top = "t" where id = "%s" ' % (id))
        if level != '4' and level != '5':
            cursor.execute('update report set readed = "y" where id = "%s"' % (id))
    except Exception, e:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('report_display.html', level=level, row=row, user_type=user_type,
                           part_type=part_type, nickname=nickname)


@app.route('/delete_report/<id>')
def delete_report(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    user_type = session.get('user_type')

    try:
        cursor.execute('update report set see = "n" where id = "%s"' % (id))
        conn.commit()
    except Exception, e:
        print e

    return redirect(url_for('report_list', part_type=user_type))


@app.route('/top/<id>')
def top(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    part_type = ''
    try:
        cursor.execute('select part_type from ja where id = "%s"' % (id))
        row = cursor.fetchone()
        part_type = row[0]
        cursor.execute('update ja set top = "t" where id = "%s" ' % (id))
        conn.commit()
    except Exception, e:
        print e
    return redirect(url_for('delete_list', part_type=part_type))


@app.route('/top_cancel/<id>')
def top_cancel(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    part_type = ''
    try:
        cursor.execute('select part_type from ja where id = "%s"' % (id))
        row = cursor.fetchone()
        part_type = row[0]
        cursor.execute('update ja set top = "n" where id = "%s" ' % (id))
        conn.commit()
    except Exception, e:
        print e
    return redirect(url_for('delete_list', part_type=part_type))


@app.route('/delete_file/<id>')
def delete_file(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    part_type = ''
    filepath = []
    deletefile = ''
    user_type = session.get('user_type')
    try:
        cursor.execute('select part_type,filename,filepath from ja where id = "%s"' % (id))
        row = cursor.fetchone()
        part_type = row[0]
        try:
            basepath = os.path.dirname(__file__)
            filepath = row[2].split(';')
            if user_type == "admin" or user_type == 'general':
                for f in filepath:
                    deletefile = basepath + '/static/uploads/' + f
                    os.remove(deletefile)
        except Exception:
            pass
    except Exception, e:
        print e

    try:
        cursor.execute('delete from ja where id = "%s"' % (id))
        cursor.execute('delete from sign where id = "%s"' % (id))
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return redirect(url_for('delete_list', part_type=part_type))


@app.route('/delete_mark/<id>')
def delete_mark(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    part_type = ''
    filepath = []
    deletefile = ''
    user_type = session.get('user_type')
    try:
        cursor.execute('select filename,filepath from mark where id = "%s"' % (id))
        row = cursor.fetchone()
        try:
            basepath = os.path.dirname(__file__)
            filepath = row[1].split(';')
            if user_type == "admin" or user_type == 'general':
                for f in filepath:
                    deletefile = basepath + '/static/uploads/' + f
                    os.remove(deletefile)

        except Exception:
            pass
    except Exception, e:
        print e

    try:
        cursor.execute('delete from mark where id = "%s"' % (id))
        cursor.execute('delete from markhandle where mid = "%s"' % (id))
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return redirect(url_for('mark_alllist'))

@app.route('/lotsdelete_mark',methods=['get', 'post'])
def lotsdelete_mark():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    part_type = ''
    filepath = []
    deletefile = ''
    user_type = session.get('user_type')
    deletelist = []
    try:
        deletelist = request.values.getlist('delete')
        for d in deletelist:
            try:
                cursor.execute('select filename,filepath from mark where id = "%s"' % (d))
                row = cursor.fetchone()
                try:
                    basepath = os.path.dirname(__file__)
                    filepath = row[1].split(';')
                    if user_type == "admin" or user_type == 'general':
                        for f in filepath:
                            deletefile = basepath + '/static/uploads/' + f
                            os.remove(deletefile)

                except Exception:
                    pass
            except Exception, e:
                print e

            try:
                cursor.execute('delete from mark where id = "%s"' % (d))
                cursor.execute('delete from markhandle where mid = "%s"' % (d))
            except Exception:
                pass
    except:
        pass








    conn.commit()
    cursor.close()
    conn.close()
    return redirect(url_for('mark_alllist'))


@app.route('/remind/<id>',methods=['get', 'post'])
def remind(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    level = session.get('level')
    nickname = session.get('nickname')
    hrow = []
    oldaddman = ''
    addman = ''
    try:
        cursor.execute('select addman,mid from markhandle where id = "%s" ' % id)
        hrow = cursor.fetchone()
        oldaddman = hrow[0]
        addman = oldaddman + ' ' + nickname
    except Exception:
        pass
    try:
        cursor.execute('update markhandle set marked = "n", addman = "%s" where id = "%s" ' % (addman,id))
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    if level == '3' or level == '4' or level == '5':
        return redirect(url_for('mark_handle', id = hrow[1]))
    return redirect(url_for('marking', id = hrow[1]))


@app.route('/send/<id>', methods=['get', 'post'])
def send(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    partname = ''
    cursor.execute('select * from ja where id = "%s"' % (id))
    row = cursor.fetchone()
    user_type = session.get('user_type')
    part_type = user_type
    session['id'] = id
    nickname = session.get('nickname')
    level = session.get('level')
    # date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    select_list = []
    # filepath = ''
    # title=''
    slen = ''
    if part_type == 'dq':
        partname = '党委办公室'
        select_list = ['组织工作', '宣传工作', '纪检工作', '工会工作', '团青工作']
        slen = select_list.__len__()
    elif part_type == 'zh':
        partname = '综合部'
        select_list = ['通知公告', '工作总结', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'yx':
        partname = '市场营销部'
        select_list = ['通知公告', '走访情况', '市场变化', '新增货源', '营销文电']
        slen = select_list.__len__()
    elif part_type == 'rl':
        partname = '人力资源部'
        select_list = ['通知公告', '人事管理', '工资定额', '规章制度', '社保年金']
        slen = select_list.__len__()
    elif part_type == 'jc':
        partname = '计划财务部'
        select_list = ['通知公告', '收入文电', '财务文电', '常用资料', '税务资料', '收入情况']
        slen = select_list.__len__()
    elif part_type == 'ja':
        partname = '技术安全部'
        select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
        slen = select_list.__len__()
    elif part_type == 'zx':
        partname = '装卸管理部'
        select_list = ['通知公告', '物资管理', '设备运用', '规章制度', '经验交流']
        slen = select_list.__len__()
    elif part_type == 'zj':
        partname = '职工教育部'
        select_list = ['通知公告', '职教文件', '培训考试', '学习计划', '岗位练兵', '培训课件', '职教台账']
        slen = select_list.__len__()
    elif part_type == 'dd':
        partname = '生产调度室'
        select_list = ['通知公告', '调度命令', '生产指标', '生产资料', '应急预案']
        slen = select_list.__len__()
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'kys':
        partname = '快运所'
        select_list = ['通知公告', '经验交流', '工作信息', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggyys':
        partname = '贵港营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'lbyys':
        partname = '来宾营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czyys':
        partname = '崇左营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
        select_list = ['通知公告', '教育培训', '安全、生产', '规章制度', '设备动态', '职工风采', '党建工会']
        slen = select_list.__len__()
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czzgs':
        partname = '崇左装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'tpxw':
        partname = '图片新闻'
        select_list = ['图片新闻']
        slen = select_list.__len__()
    else:
        pass
    filed = []
    filed = row[3].split(';')
    flen = filed.__len__()
    filepath = row[9].split(';')
    session['filepath1'] = filepath
    users = []
    try:
        cursor.execute('select * from users  order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    return render_template('send.html', row=row, user_type=user_type, part_type=part_type, partname=partname,
                           users=users,
                           nickname=nickname, select_list=select_list, slen=slen, level=level, flen=flen, filed=filed)


@app.route('/send_submit', methods=['get', 'post'])
def send_submit():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    id = ''
    part_type = ''
    id = session.get('id')
    part_type = session.get('part_type')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    level = session.get('level')
    content = ''
    filenames = ''
    filepaths = ''
    filepathed = ''
    type = ''
    sign_type = ''
    fileds = []
    filed = ''
    approver = ''
    publisher = ''
    number = ''
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    try:
        cursor.execute('select * from ja where id = "%s"' % (id))
        row = cursor.fetchone()
        filed = row[3]
        filepath = row[9]
        publisher = row[10]
        approver = row[11]
        number = row[12]
    except Exception:
        pass

    try:
        if request.method == 'POST':

            basepath = os.path.dirname(__file__)
            title = request.values.get('title')
            content1 = request.values.get('content')
            content = MySQLdb.escape_string(content1)
            type = request.values.get('text_type')
            sign_types = request.values.getlist('sign_type')
            top = request.values.get('top')
            # fileds = request.values.getlist('filed')
            # filepath = session.get('filepath1')
            for i in sign_types:
                sign_type = sign_type + i + ";"
            for i in range(0, fileds.__len__()):
                # if fileds[i].__len__() > 0:
                filed = filed + fileds[i] + ';'
                filepathed = filepathed + filepath[i] + ';'

        filenames = filed
        filepaths = filepath
    except Exception, e:
        pass

    if title.__len__() > 0:
        if level == '3':
            try:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number) value('%s','%s','%s','%s','%s','n','%s','%s','%s','%s','%s')" % (
                        title, content, filenames, date, type, sign_type, filepaths, publisher,approver,number))
            except Exception:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number) value('%s','%s','','%s','%s','%s','%s','','%s','%s','%s')" % (
                        title, content, date, type, user_type, sign_type, publisher,approver,number))
        elif level == '4':
            try:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number) value('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                        title, content, filenames, date, type, user_type, sign_type, filepaths, publisher,approver,number))
            except Exception:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname,approver,number) value('%s','%s','','%s','%s','%s','%s','','%s','%s','%s')" % (
                        title, content, date, type, user_type, sign_type, publisher,approver,number))
        else:
            pass
    else:
        pass
    conn.commit()

    try:
        cursor.execute('select id from ja order by id desc limit 1')
        jid = cursor.fetchone()
        ulen = cursor.execute('select username,nickname,user_type from users')
        us = cursor.fetchall()

        n = cursor.execute('select id from sign where id = "%s" ' % (jid[0]))
        if n == 0:
            for i in range(0, ulen):
                if us[i][0] in sign_types:
                    cursor.execute(
                        'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                            jid[0], us[i][0], us[i][1], us[i][2]))
        else:
            pass
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    if level == '3':
        return redirect(url_for('ja_display', id=jid[0]))
    return redirect(url_for('part_manage', part_type=user_type))


@app.route('/report_send/<id>', methods=['get', 'post'])
def report_send(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    cursor.execute('select * from report where id = "%s"' % (id))
    row = cursor.fetchone()
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    level = session.get('level')
    session['id2'] = id

    filed = row[3]
    filepath = row[4]
    session['filepath1'] = filepath
    users = []
    try:
        cursor.execute('select * from users  order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    return render_template('report_send.html', row=row, users=users, user_type=user_type, part_type=user_type,
                           nickname=nickname, filed=filed)


@app.route('/reportsend_submit', methods=['get', 'post'])
def reportsend_submit():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    id = ''
    id = session.get('id2')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    level = session.get('level')
    content = ''
    filenames = ''
    filepaths = ''
    filepathed = ''
    type = ''
    sign_type = ''
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    try:
        cursor.execute('select * from report where id = "%s"' % (id))
        row = cursor.fetchone()
        filenames = row[3]
        filepaths = row[4]
    except Exception:
        pass

    try:
        if request.method == 'POST':
            title = request.values.get('title')
            content1 = request.values.get('content')
            content = MySQLdb.escape_string(content1)
            type = request.values.get('text_type')
            sign_types = request.values.getlist('sign_type')

            for i in sign_types:
                sign_type = sign_type + i + ";"
    except Exception, e:
        pass

    if title.__len__() > 0:
        if level == '3' or level == '4':
            try:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname) value('%s','%s','%s','%s','%s','n','%s','%s','%s')" % (
                        title, content, filenames, date, type, sign_type, filepaths, nickname))
            except Exception:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname) value('%s','%s','','%s','%s','%s','%s','','%s')" % (
                        title, content, date, type, user_type, sign_type, nickname))
        else:
            pass
    else:
        pass
    conn.commit()

    try:
        cursor.execute('select id from ja order by id desc limit 1')
        jid = cursor.fetchone()
        ulen = cursor.execute('select username,nickname,user_type from users')
        us = cursor.fetchall()

        n = cursor.execute('select id from sign where id = "%s" ' % (jid[0]))
        if n == 0:
            for i in range(0, ulen):
                if us[i][0] in sign_types:
                    cursor.execute(
                        'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                            jid[0], us[i][0], us[i][1], us[i][2]))
        else:
            pass
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    if level == '3' or level == '4':
        return redirect(url_for('ja_display', id=jid[0]))
    return redirect(url_for('part_manage', part_type=user_type))


@app.route('/edit/<id>', methods=['get', 'post'])
def edit(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    partname = ''
    cursor.execute('select * from ja where id = "%s"' % (id))
    row = cursor.fetchone()
    part_type = row[6]
    type = row[5]
    session['id'] = id
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    level = session.get('level')
    select_list = []
    filepath = ''
    if part_type == 'dq':
        partname = '党委办公室'
        select_list = ['组织工作', '宣传工作', '纪检工作', '工会工作', '团青工作']
        slen = select_list.__len__()
    elif part_type == 'zh':
        partname = '综合部'
        select_list = ['通知公告', '工作总结', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'yx':
        partname = '市场营销部'
        select_list = ['通知公告', '走访情况', '市场变化', '新增货源', '营销文电']
        slen = select_list.__len__()
    elif part_type == 'rl':
        partname = '人力资源部'
        select_list = ['通知公告', '人事管理', '工资定额', '规章制度', '社保年金']
        slen = select_list.__len__()
    elif part_type == 'jc':
        partname = '计划财务部'
        select_list = ['通知公告', '收入文电', '财务文电', '常用资料', '税务资料', '收入情况']
        slen = select_list.__len__()
    elif part_type == 'ja':
        partname = '技术安全部'
        select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
        slen = select_list.__len__()
    elif part_type == 'zx':
        partname = '装卸管理部'
        select_list = ['通知公告', '物资管理', '设备运用', '规章制度', '经验交流']
        slen = select_list.__len__()
    elif part_type == 'zj':
        partname = '职工教育部'
        select_list = ['通知公告', '职教文件', '培训考试', '学习计划', '岗位练兵', '培训课件', '职教台账']
        slen = select_list.__len__()
    elif part_type == 'dd':
        partname = '生产调度室'
        select_list = ['通知公告', '调度命令', '生产指标', '生产资料', '应急预案']
        slen = select_list.__len__()
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'kys':
        partname = '快运所'
        select_list = ['通知公告', '经验交流', '工作信息', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggyys':
        partname = '贵港营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'lbyys':
        partname = '来宾营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czyys':
        partname = '崇左营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
        select_list = ['通知公告', '教育培训', '安全、生产', '规章制度', '设备动态', '职工风采', '党建工会']
        slen = select_list.__len__()
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czzgs':
        partname = '崇左装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    else:
        pass

    if type == 'tpxw':
        partname = '图片新闻'
        select_list = ['图片新闻']
        slen = select_list.__len__()
    elif type == 'jcwt':
        partname = '基层动态'
        select_list = ['基层动态']
        slen = select_list.__len__()
    else:
        pass
    filed = []
    filed = row[3].split(';')
    flen = filed.__len__()
    filepath = row[9].split(';')
    session['filepath'] = filepath
    users = []
    optionnum = 0
    try:
        optionnum = int(row[5])
    except Exception:
        pass
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass

    return render_template('edit.html', list=row, user_type=user_type, part_type=part_type,
                           partname=partname, nickname=nickname, select_list=select_list, slen=slen, filed=filed,
                           flen=flen, level=level, type=type, users=users, optionnum=optionnum)


@app.route('/edit_submit', methods=['get', 'post'])
def edit_submit():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    id = ''
    part_type = ''
    id = session.get('id')
    part_type = session.get('part_type')
    user_type = session.get('user_type')
    level = session.get('level')
    content = ''
    filename = ''
    filename2 = ''
    filename3 = ''
    filename4 = ''
    filenames = ''
    filepaths = ''
    filepathed = ''
    type = ''
    sign_type = ''
    fileds = []
    filed = ''
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))

    try:
        if request.method == 'POST':

            basepath = os.path.dirname(__file__)
            title = request.values.get('title')
            content1 = request.values.get('content')
            content = MySQLdb.escape_string(content1)
            type = request.values.get('text_type')
            sign_types = request.values.getlist('sign_type')
            top = request.values.get('top')
            fileds = request.values.getlist('filed')
            filepath = session.get('filepath')
            for i in sign_types:
                sign_type = sign_type + i + ";"
            for i in range(0, fileds.__len__()):
                if fileds[i].__len__() > 0:
                    filed = filed + fileds[i] + ';'
                    filepathed = filepathed + filepath[i] + ';'

            filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt2 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt3 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            salt4 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
            rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt1)
            rnd2 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt2)
            rnd3 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt3)
            rnd4 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt4)

            try:
                file1 = request.files['file1']
                filename = file1.filename
                if filename.__len__() > 0:
                    fname, fext = os.path.splitext(filename)
                    rnd_name1 = '%s%s' % (rnd1, fext)
                    upload_path1 = os.path.join(basepath, 'static\uploads', rnd_name1)
                    file1.save(upload_path1)
                    filepaths = rnd_name1
                    filenames = filename


            except Exception:
                pass

            try:
                file2 = request.files['file2']
                filename2 = file2.filename
                if filename2.__len__() > 0:
                    fname, fext = os.path.splitext(filename2)
                    rnd_name2 = '%s%s' % (rnd2, fext)
                    upload_path2 = os.path.join(basepath, 'static\uploads', rnd_name2)
                    file2.save(upload_path2)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name2
                        filenames = filenames + filename2
                    else:
                        filepaths = filepaths + ';' + rnd_name2
                        filenames = filenames + ';' + filename2


            except Exception:
                pass

            try:
                file3 = request.files['file3']
                filename3 = file3.filename
                if filename3.__len__() > 0:
                    fname, fext = os.path.splitext(filename3)
                    rnd_name3 = '%s%s' % (rnd3, fext)
                    upload_path3 = os.path.join(basepath, 'static\uploads', rnd_name3)
                    file3.save(upload_path3)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name3
                        filenames = filenames + filename3
                    else:
                        filepaths = filepaths + ';' + rnd_name3
                        filenames = filenames + ';' + filename3


            except Exception:
                pass

            try:
                file4 = request.files['file4']
                filename4 = file4.filename
                if filename4.__len__() > 0:
                    fname, fext = os.path.splitext(filename4)
                    rnd_name4 = '%s%s' % (rnd4, fext)
                    upload_path4 = os.path.join(basepath, 'static\uploads', rnd_name4)
                    file4.save(upload_path4)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name4
                        filenames = filenames + filename4
                    else:
                        filepaths = filepaths + ';' + rnd_name4
                        filenames = filenames + ';' + filename4

            except Exception:
                pass
            filenames = filed + filenames
            filepaths = filepathed + filepaths
    except Exception, e:
        pass

    if title.__len__() > 0:
        try:
            cursor.execute(
                'update ja set title = "%s",content = "%s",filename = "%s",time="%s",type="%s",sign_type="%s",filepath="%s" WHERE id="%s"' % (
                    title, content, filenames, date, type, sign_type, filepaths, id))
            try:

                ulen = cursor.execute('select username,nickname,user_type,level from users')
                us = cursor.fetchall()
                if level == '1' or level == '2' or level == '3':
                    for i in range(0, ulen):
                        if us[i][0] in sign_types and (us[i][3] == '3' or us[i][3] == '4'):
                            n = cursor.execute('select uid from sign where id="%s" and username="%s"' % (id, us[i][0]))
                            if n == 0:
                                cursor.execute(
                                    'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                                        id, us[i][0], us[i][1], us[i][2]))
                    cursor.execute('select * from sign where id = "%s" ' % id)
                    listed = cursor.fetchall()
                    for l in listed:
                        if l[2] in sign_types:
                            pass
                        else:
                            cursor.execute('delete from sign where id="%s" and username="%s"' % (id, l[2]))
                elif level == '4':
                    for i in range(0, ulen):
                        if us[i][0] in sign_types and us[i][3] == '5':
                            n = cursor.execute('select uid from sign where id="%s" and username="%s"' % (id, us[i][0]))
                            if n == 0:
                                cursor.execute(
                                    'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                                        id, us[i][0], us[i][1], us[i][2]))
                    cursor.execute('select * from sign where id = "%s" ' % id)
                    listed = cursor.fetchall()
                    for l in listed:
                        if l[2] in sign_types:
                            pass
                        else:
                            cursor.execute('delete from sign where id="%s" and username="%s"' % (id, l[2]))
                    conn.commit()
                    return redirect(url_for('part_manage', part_type=user_type))
                else:
                    pass
            except Exception:
                pass
        except Exception, e:
            cursor.execute(
                'update ja set title = "%s",content = "%s",time="%s",type="%s",sign_type="%s" WHERE id="%s"' % (
                    title, content, date, type, sign_type, id))
    else:
        pass

    conn.commit()
    conn.close()
    if user_type == 'admin' or user_type == 'general':
        pass
    else:
        return redirect(url_for('part_manage', part_type=user_type))
    return redirect(url_for('delete_list', part_type=part_type))


@app.route('/software_output', methods=['get', 'post'])
def software_output():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    filename = ''
    title = ''
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 10000000)), salt1)

    try:
        if request.method == 'POST':
            title = request.values.get('title')
            content = request.values.get('content')
            file = request.files['file']
            basepath = os.path.dirname(__file__)
            filename = file.filename
            if filename.__len__() > 0:
                fname, fext = os.path.splitext(filename)
                rnd_name1 = '%s%s' % (rnd1, fext)
                upload_path = os.path.join(basepath, 'static\uploads', rnd_name1)
                file.save(upload_path)
    except Exception, e:
        pass
    if title.__len__() > 0:
        try:
            cursor.execute('insert into software(filename,content,time,file) value("%s","%s","%s","%s")' % (
                title, content, date, rnd_name1))
        except Exception:
            pass
    else:
        pass
    conn.commit()
    conn.close()
    return render_template('software_output.html', nickname=nickname)


@app.route('/software_manage')
def software_manage():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    list = []
    try:
        cursor.execute('select * from software')
        rows = cursor.fetchall()
        for row in rows:
            list.append(row)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('software_manage.html', list=list, nickname=nickname)


@app.route('/software_delete/<id>')
def software_delete(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    try:
        cursor.execute('select * from software where id ="%s"' % (id))
        row = cursor.fetchone()
        file = row[4]
        basepath = os.path.dirname(__file__)
        deletefile = basepath + '/static/uploads/' + file
        os.remove(deletefile)
    except Exception, e:
        print e

    try:
        cursor.execute('delete from software where id = "%s"' % (id))
    except Exception:
        pass
    conn.commit()
    return redirect(url_for('software_manage'))


@app.route('/software')
def software():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    try:
        cursor.execute("select * from software order by id desc")
        rows = cursor.fetchall()
        for row in rows:
            list.append(row)
    except Exception, e:
        print e
    session['software'] = list
    return render_template('software.html', list=list)


@app.route('/software_introduce/<id>')
def software_introduce(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    try:
        cursor.execute("select * from software where id = '%s'" % id)
        row = cursor.fetchone()
    except Exception, e:
        print e
    return render_template('software_introduce.html', row=row)


@app.route('/password_modify', methods=['get', 'post'])
def password_modify():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    username = session.get('username')
    user_type = session.get('user_type')
    old_password = session.get('password')
    old = request.values.get('old_password')
    new = request.values.get('new_password1')
    new2 = request.values.get('new_password2')
    password = session.get('password')
    p = ''
    if old == password and new == new2:
        p = '密码修改成功，请重新登录'
        try:
            cursor.execute('UPDATE users SET password = "%s" WHERE username  = "%s" ' % (new, username))
            conn.commit()
        except Exception, e:
            p = '密码修改失败'
    else:
        pass
    cursor.close()
    conn.close()

    return render_template('password_modify.html', nickname=nickname, p=p, username=username, old_password=old_password,
                           user_type=user_type)


@app.route('/upassword_modify', methods=['get', 'post'])
def upassword_modify():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    username = session.get('username')
    user_type = session.get('user_type')
    old_password = session.get('password')

    old = request.values.get('old_password')
    new = request.values.get('new_password1')
    new2 = request.values.get('new_password2')
    p = ''
    if old == old_password and new == new2:
        p = '密码修改成功，请重新登录'
        try:
            cursor.execute('UPDATE users SET password = "%s" WHERE username  = "%s" ' % (new, username))
            conn.commit()
        except Exception, e:
            p = '密码修改失败'
    else:
        pass

    cursor.close()
    conn.close()

    return render_template('upassword_modify.html', nickname=nickname, p=p, username=username,
                           old_password=old_password, user_type=user_type)


@app.route('/user_add', methods=['get', 'post'])
def user_add():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    user_type = ''
    username = ''
    password = ''
    nickname = ''
    post = ''
    level = ''
    sort = ''
    ranking = ''
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    try:
        if request.method == 'POST':
            user_type = request.values.get('user_type')
            username = request.values.get('username')
            password = request.values.get('password')
            nickname = request.values.get('nickname')
            sign = request.values.get('sign')
            post = request.values.get('post')
            ranking = request.values.get('ranking')


        if user_type == 'general':
            level = '2'
            sort = 'n'
        elif user_type == 'ja' or user_type == 'dq' or user_type == 'zh' or user_type == 'yx' \
                or user_type == 'dd' or user_type == 'zx' or user_type == 'zj' or user_type == 'rl' or user_type == 'jc':
            level = '3'
            sort = '1'
        elif user_type == 'nnnyys' or user_type == 'ggyys' or user_type == 'lbyys' or user_type == 'czyys' \
                or user_type == 'kys':
            level = '4'
            sort = '2'
        elif user_type == 'nnnzgs' or user_type == 'ggzgs' or user_type == 'ltzgs' or user_type == 'czzgs':
            level = '4'
            sort = '3'
        elif user_type == 'nnjfgs' or user_type == 'nnjxs' or user_type == 'nnncwls' or user_type == 'nnzls':
            level = '5'
            sort = '4'
        elif user_type == 'zxld':
            level = '8'
            sort = '8'
        if sign == 'y':
            level = '5'
            sort = '5'
    except Exception, e:
        pass

    if user_type.__len__() > 0 and username.__len__() > 0 and password.__len__() > 0:
        try:
            cursor.execute(
                "insert into users(username,password,user_type,nickname,post,level,sort,ranking) value('%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    username, password, user_type, nickname, post, level, sort,ranking))
        except Exception:
            pass
    else:
        pass
    conn.commit()
    cursor.close()
    conn.close()

    return render_template('user_add.html', nickname=nickname)


@app.route('/user_manage', methods=['get', 'post'])
def user_manage():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    list = []
    try:
        cursor.execute('select * from users  order by ranking')
        rows = cursor.fetchall()
        for row in rows:
            if row[2] == 'admin':
                pass
            else:
                list.append(row)
                # for row in rows:
                #     if row[5] == '3':
                #         list.append(row)
                # for row in rows:
                #     if row[5] == '4':
                #         list.append(row)
                # for row in rows:
                #     if row[5] == '5':
                #         list.append(row)

    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('user_manage.html', list=list, nickname=nickname)


@app.route('/user_class/<part_type>', methods=['get', 'post'])
def user_class(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    list = []
    try:
        cursor.execute('select * from users where user_type = "%s" and level ="5" order by ranking' % part_type)
        rows = cursor.fetchall()
        for row in rows:
            if row[2] == 'admin':
                pass
            else:
                list.append(row)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('user_class.html', nickname=nickname, list=list)


@app.route('/user_delete/<username>', methods=['get', 'post'])
def user_delete(username):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    session['username'] = username
    try:
        cursor.execute('delete from users where username = "%s"' % (username))
        conn.commit()
    except Exception:
        pass
    cursor.close()
    conn.close()
    return redirect(url_for('user_manage'))


@app.route('/user_edit/<username>', methods=['get', 'post'])
def user_edit(username):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    session['username'] = username
    try:
        cursor.execute('select * from users where username = "%s" ' % username)
        row = cursor.fetchone()
    except Exception:
        pass
    return render_template('user_edit.html', row=row)


@app.route('/useredit_submit', methods=['get', 'post'])
def useredit_submit():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    username = session.get('username')
    sort = ''
    ranking = ''
    try:
        if request.method == 'POST':
            user_type = request.values.get('user_type')
            username1 = request.values.get('username')
            password = request.values.get('password')
            nickname = request.values.get('nickname')
            post = request.values.get('post')
            sign = request.values.get('sign')
            ranking = request.values.get('ranking')
            if user_type == 'general':
                level = '2'
                sort = 'n'
            elif user_type == 'ja' or user_type == 'dq' or user_type == 'zh' or user_type == 'yx' \
                    or user_type == 'dd' or user_type == 'zx' or user_type == 'zj' or user_type == 'rl' or user_type == 'jc':
                level = '3'
                sort = '1'
            elif user_type == 'nnnyys' or user_type == 'ggyys' or user_type == 'lbyys' or user_type == 'czyys' \
                    or user_type == 'kys':
                level = '4'
                sort = '2'
            elif user_type == 'nnnzgs' or user_type == 'ggzgs' or user_type == 'ltzgs' or user_type == 'czzgs':
                level = '4'
                sort = '3'
            elif user_type == 'nnjfgs' or user_type == 'nnjxs' or user_type == 'nnncwls' or user_type == 'nnzls':
                level = '5'
                sort = '4'
            elif user_type == 'zxld':
                level = '8'
            if sign == 'y':
                level = '5'
                sort = '5'
    except Exception, e:
        return url_for('permit')

    if user_type.__len__() > 0 and username1.__len__() > 0 and password.__len__() > 0:
        try:
            cursor.execute(
                "update users set username = '%s',password='%s',user_type='%s',nickname='%s',post='%s',level='%s',sort='%s',ranking='%s' where username = '%s'" % (
                    username1, password, user_type, nickname, post, level, sort, ranking, username))
        except Exception:
            pass
    else:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return redirect(url_for('user_manage'))


@app.route('/tabloid_display/<id>')
def tabloid_display(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    picture = []
    try:
        cursor.execute('select * from ja where part_type = "tpxw" and id = "%s"' % id)
        prow = cursor.fetchone()
        pictures = prow[3].split(';')
        for p in pictures:
            if p.__len__() > 0:
                picture.append(p)
    except Exception:
        pass
    return render_template('tabloid_display.html', prow=prow, picture=picture)


@app.route('/tabloid_output', methods=['get', 'post'])
def tabloid_output():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    try:
        if request.method == 'POST':
            file = request.files['file']
            filename = file.filename
            basepath = os.path.dirname(__file__)
            upload_path = os.path.join(basepath, 'static\img', filename)
            file.save(upload_path)
    except Exception, e:
        pass

    try:
        cursor.execute("insert into picture(filename,time) value('%s','%s')" % (filename, date))
    except Exception:
        pass
    else:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('tabloid_output.html', nickname=nickname)


@app.route('/tabloid_manage')
def tabloid_manage():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    list = []
    try:
        cursor.execute('select * from picture order by id desc')
        rows = cursor.fetchall()
        for row in rows:
            list.append(row)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('tabloid_manage.html', list=list, nickname=nickname)


@app.route('/tabloid_delete/<id>')
def tabloid_delete(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    try:
        cursor.execute('delete from picture where id = "%s"' % (id))
        conn.commit()
    except Exception:
        pass
    cursor.close()
    conn.close()
    return redirect(url_for('tabloid_manage'))


@app.route('/links')
def links():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    try:
        cursor.execute("select * from links")
        rows = cursor.fetchall()
        for row in rows:
            list.append(row)
    except Exception, e:
        print e

    return render_template('links.html', list=list)


@app.route('/links_output', methods=['get', 'post'])
def links_output():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()

    linksname = request.values.get('linksname')
    site = request.values.get('site')
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    try:
        if linksname.__len__() > 0 and site.__len__() > 0:
            cursor.execute("insert into links(linksname,site) value('%s','%s')" % (linksname, site))
    except Exception, e:
        print e

    conn.commit()
    cursor.close()
    conn.close()

    return render_template('links_output.html', nickname=nickname)


@app.route('/links_manage')
def links_manage():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    if user_type == 'admin':
        pass
    else:
        return redirect(url_for('permit'))

    list = []
    try:
        cursor.execute('select * from links')
        rows = cursor.fetchall()
        for row in rows:
            list.append(row)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('links_manage.html', list=list, nickname=nickname)


@app.route('/links_delete/<id>')
def links_delete(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    try:
        cursor.execute('delete from links where id = "%s"' % (id))
        conn.commit()
    except Exception:
        pass
    cursor.close()
    conn.close()
    return redirect(url_for('links_manage'))


@app.route('/commonword', methods=['get', 'post'])
def commonword():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    words = []
    word = ''
    if request.method == 'POST':
        word = request.values.get('word')
        if word.__len__() > 0:
            try:
                cursor.execute('insert into commonword(commonword) value("%s") ' % word)
            except Exception:
                pass
    try:
        cursor.execute('select * from commonword order by id desc')
        words = cursor.fetchall()
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('commonword.html', words=words)


@app.route('/delete_commonword/<id>', methods=['get', 'post'])
def delete_commonword(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    try:
        cursor.execute('delete from commonword where id = "%s"' % (id))
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return redirect(url_for('commonword'))


@app.route('/sign/<id>', methods=['get', 'post'])
def sign(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    username = session.get('username')
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    try:
        cursor.execute(
            'UPDATE sign SET state = "y", time = "%s" WHERE username  = "%s" and id = "%s"' % (date, username, id))
        conn.commit()
    except Exception:
        pass

    return redirect(url_for('ja_display', id=id))


@app.route('/signed_list/<username>', methods=['get', 'post'])
def signed_list(username):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    username1 = session.get('username')
    level = session.get('level')
    list = []
    sign_list = []
    sign_type = []
    try:
        cursor.execute('select * from sign where username = "%s" and state = "y" order by id desc' % (username))
        sign_list = cursor.fetchall()
        for s in sign_list:
            cursor.execute('select * from ja where id = "%s"' % (s[1]))
            row = cursor.fetchone()
            list.append(row)
            # sign_type = row[7].split(';')
            # if s[2] in sign_type:  # 若该部门需签收且未签收，则添加到未签收列表中
            #     list.append(row)
            # else:
            #     pass
    except Exception:
        pass
    return render_template('sign_list.html', list=list, username=username, level=level)


@app.route('/sign_list/<username>', methods=['get', 'post'])
def sign_list(username):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    username1 = session.get('username')
    level = session.get('level')
    list = []
    sign_list = []
    sign_type = []
    try:
        cursor.execute('select * from sign where username = "%s" and state = "n" order by id desc' % (username))
        sign_list = cursor.fetchall()
        for s in sign_list:
            cursor.execute('select * from ja where id = "%s"' % (s[1]))
            row = cursor.fetchone()
            list.append(row)
            # sign_type = row[7].split(';')
            # if s[2] in sign_type:  # 若该部门需签收且未签收，则添加到未签收列表中
            #     list.append(row)
            # else:
            #     pass
    except Exception:
        pass
    return render_template('sign_list.html', list=list, username=username, level=level)


@app.route('/mark_class/<part_type>', methods=['get', 'post'])
def mark_class(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    today = datetime.datetime.strptime(str(date), '%Y-%m-%d')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nlist = []
    mlist = []
    n = 0
    l = 0
    days = 0
    try:
        cursor.execute(
            'select * from users where level = "5" and user_type ="%s"' % (part_type))
        users = cursor.fetchall()
    except Exception:
        pass

    try:
        for u in users:
            l = 0
            n = cursor.execute(
                'select * from markhandle where markman = "%s" and marked = "n"' % u[0])  # 取该登录用户未签收的所有消息
            mlist = cursor.fetchall()
            for um in mlist:
                days = (today - datetime.datetime.strptime(str(um[10]), '%Y-%m-%d')).days
                if days > 0:
                    l = l + 1
            m = (u[0], u[3], n, u[5], l)
            nlist.append(m)
    except Exception:
        pass

    return render_template('mark_class.html', nlist=nlist, user_type=user_type, nickname=nickname)


@app.route('/sign_class/<part_type>', methods=['get', 'post'])
def sign_class(part_type):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nlist = []
    n = 0
    try:
        cursor.execute(
            'select * from users where level = "5" and user_type ="%s"' % (part_type))
        users = cursor.fetchall()
    except Exception:
        pass

    try:
        for u in users:
            n = cursor.execute(
                'select * from sign where username = "%s" and state = "n"' % u[0])  # 取该登录用户未签收的所有消息
            m = (u[0], u[3], n, u[5])
            nlist.append(m)
    except Exception:
        pass

    return render_template('sign_class.html', nlist=nlist, user_type=user_type, nickname=nickname)


@app.route('/marked_list/<username>', methods=['get', 'post'])
def marked_list(username):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    username1 = session.get('username')
    level = session.get('level')
    list = []
    sign_list = []
    sign_type = []
    try:
        cursor.execute('select * from markhandle where markman = "%s" and marked = "n" order by id desc' % (username))
        sign_list = cursor.fetchall()
        for s in sign_list:
            cursor.execute('select * from mark where id = "%s"' % (s[1]))
            row = cursor.fetchone()
            list.append(row)
    except Exception:
        pass
    return render_template('marked_list.html', list=list, username=username)


@app.route('/mark_count')
def mark_count():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    today = datetime.datetime.strptime(str(date), '%Y-%m-%d')
    mlist = []
    nlist = []
    days = 0
    n = 0
    l = 0
    try:
        cursor.execute(
            'select * from users where level = "3" or level = "4" or level = "5" order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass

    try:
        for u in users:
            l = 0
            n = cursor.execute(
                'select * from markhandle where markman = "%s" and marked = "n"' % (u[0]))  # 取该登录用户未签收的所有消息
            mlist = cursor.fetchall()
            for um in mlist:
                days = (today - datetime.datetime.strptime(str(um[10]), '%Y-%m-%d')).days
                if days > 0:
                    l=l+1
            m = (u[0], u[3], n, u[5], l)
            nlist.append(m)
    except Exception:
        pass
    return render_template('mark_count.html', nlist=nlist, user_type=user_type, nickname=nickname)

@app.route('/marknoh_count')
def marknoh_count():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nlist = []
    n = 0
    try:
        cursor.execute(
            'select * from users where level = "3" or level = "4" or level = "5"')
        users = cursor.fetchall()
    except Exception:
        pass

    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 获取当前时间
    today = datetime.datetime.strptime(str(date), '%Y-%m-%d')

    try:
        for u in users:
            n = cursor.execute(
                'select * from markhandle where markman = "%s" and marked = "n"' % u[0])  # 取该登录用户未签收的所有消息

            days = (today - datetime.datetime.strptime(u[4], '%Y-%m-%d')).days
            m = (u[0], u[3], n, u[5])
            nlist.append(m)
    except Exception:
        pass
    return render_template('mark_count.html', nlist=nlist, user_type=user_type, nickname=nickname)


@app.route('/sign_count')
def sign_count():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nlist = []
    n = 0
    try:
        cursor.execute(
            'select * from users where level = "3" or level = "4" or level = "5" order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass

    try:
        for u in users:
            n = cursor.execute(
                'select * from sign where username = "%s" and state = "n"' % u[0])  # 取该登录用户未签收的所有消息
            m = (u[0], u[3], n, u[5])
            nlist.append(m)
    except Exception:
        pass

    return render_template('sign_count.html', nlist=nlist, user_type=user_type, nickname=nickname)


@app.route('/content', methods=['get', 'post'])
def content():
    c = session.get('content')
    return c


@app.route('/ja_display/<id>', methods=['get', 'post'])
def ja_display(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    row = []
    partname = ''
    filename = ''
    filepath = ''
    try:
        cursor.execute('select * from ja where id = "%s"' % (id))
        row = cursor.fetchone()
    except Exception, e:
        pass

    if row[6] == 'dq':
        partname = '党委办公室'
    elif row[6] == 'zh':
        partname = '综合部'
    elif row[6] == 'yx':
        partname = '市场营销部'
    elif row[6] == 'rl':
        partname = '人力资源部'
    elif row[6] == 'jc':
        partname = '计划财务部'
    elif row[6] == 'ja':
        partname = '技术安全部'
    elif row[6] == 'zx':
        partname = '装卸管理部'
    elif row[6] == 'zj':
        partname = '职工教育部'
    elif row[6] == 'dd':
        partname = '生产调度室'
    elif row[6] == 'nnnyys':
        partname = '南宁南营业所'
    elif row[6] == 'kys':
        partname = '快运所'
    elif row[6] == 'ggyys':
        partname = '贵港营业所'
    elif row[6] == 'lbyys':
        partname = '来宾营业所'
    elif row[6] == 'czyys':
        partname = '崇左营业所'
    elif row[6] == 'nnnzgs':
        partname = '南宁南装管所'
    elif row[6] == 'ggzgs':
        partname = '贵港装管所'
    elif row[6] == 'ltzgs':
        partname = '黎塘装管所'
    elif row[6] == 'czzgs':
        partname = '崇左装管所'
    else:
        pass
    global content
    session['content'] = row[2]
    filename = row[3].split(';')
    filepath = row[9].split(';')
    sign_type = []
    sign_type = row[7].split(';')
    slen = sign_type.__len__()
    sign_list = []
    fsign_list = []
    slist = []
    # nosign = ['ljyyb','tlyyb']

    try:
        # for i in range(0, slen - 1):
        #     cursor.execute('select * from sign where id = "%s" and user_type = "%s"' % (id, sign_type[i]))
        #     signs = cursor.fetchall()
        #     for s in signs:
        #         sign_list.append(s)
        cursor.execute('select * from sign where id = "%s" ' % (id))
        fsign_list = cursor.fetchall()
        for f in fsign_list:
            cursor.execute('select * from users where username = "%s" ' % f[2])
            user = cursor.fetchone()
            sg = f + tuple(user[5]) + tuple(str(user[7]))
            sign_list.append(sg)
        slist = sorted(sign_list,key=lambda sign_list: sign_list[8:])

    except Exception:
        pass



    usign = ''
    user_type = ''
    state = 'n'
    level = ''
    if 'username' in session:
        username = session.get('username')
        user_type = session.get('user_type')
        level = session.get('level')
        state = 'y'
        # if username in sign_type:
        #     pass
        # elif user_type in sign_type:
        #     pass
        # else:
        #     username = 'none'
        try:
            m = cursor.execute('select state from sign where username = "%s" and id = "%s"' % (username, id))
            usign = cursor.fetchone()
            if m == 0:
                username = 'none'
        except Exception:
            pass
    else:
        username = 'none'

    return render_template('ja_display.html', state=state, row=row, partname=partname, filename=filename,
                           username=username, user_type=user_type, level=level, usign=usign, id=id, sign_list=slist,
                           test=fsign_list,
                           filepath=filepath)


@app.route('/background')
def background():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    nickname = session.get('nickname')
    user_type = session.get('user_type')
    return render_template('background.html', nickname=nickname, user_type=user_type)


@app.route('/permit')
def permit():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    return render_template('permit.html')


@app.route('/introduce_input', methods=['GET', 'POST'])
def introduce_input():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    introduce = ''
    if request.method == 'POST':
        introduce = request.values.get('content')
        try:
            cursor.execute('insert into introduce(introduce) value("%s")' % introduce)
        except Exception:
            pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('introduce_input.html')


@app.route('/introduce')
def introduce():
    return render_template('introduce.html')


@app.route('/introduce_content', methods=['GET', 'POST'])
def introduce_content():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    introduce = []
    content = ''
    try:
        cursor.execute('select * from introduce order by id desc limit 1')
        introduce = cursor.fetchone()
        content = introduce[1]
    except Exception:
        pass

    return content


@app.route("/download/<filename>", methods=['GET', 'POST'])
def download_file(filename):
    # response = make_response(res)
    # response.headers["Content-Type"] = "text/html"
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    # name = ''
    # try:
    #     cursor.execute('select filename from ja where filepath = "%s" ' % filename)
    #     row = cursor.fetchone()
    #     name = row[0]
    # except Exception,e:
    #     name = filename
    #     pass

    directory = os.path.join(app.root_path, 'static\uploads')
    response = make_response(send_from_directory(directory, filename, as_attachment=True))
    response.headers["Content-Type"] = "text/html"
    response.headers["Content-Disposition"] = "attachment; filename={}".format(filename.encode().decode('utf-8'))
    # response.headers["Content-Disposition"] = "attachment; filename={}".format(name.encode().decode('utf-8'))
    return response


@app.route("/filedownload/<filename>", methods=['GET', 'POST'])
def filedownload(filename):
    directory = os.path.join(app.root_path, 'static\mark')
    response = make_response(send_from_directory(directory, filename, as_attachment=True))
    response.headers["Content-Type"] = "text/html"
    response.headers["Content-Disposition"] = "attachment; filename={}".format(filename.encode().decode('utf-8'))
    return response


@app.route('/list/<type>')
def list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute(
                'select * from ja WHERE (part_type="ja" or part_type="yx" or part_type="dd" or part_type="zx" '
                'or part_type="zj" or part_type="rl" or part_type="jc" or part_type="dq" or part_type="zh") order by id desc limit 200')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
        except Exception, e:
            pass
    elif type == 'jcwt':
        try:
            cursor.execute(
                'select * from ja WHERE type ="%s" order by id desc ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
        except Exception, e:
            pass
    elif type == '4':
        try:
            cursor.execute(
                'select * from ja WHERE type ="%s" and (part_type="ja" or part_type="zx" or  part_type="rl" '
                'or part_type="zh" ) ORDER BY id DESC' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
        except Exception, e:
            pass
    else:
        try:
            cursor.execute(
                'select * from ja WHERE type ="%s" and (part_type="ja" or part_type="yx" or part_type="dd" or part_type="zx" '
                'or part_type="zj" or part_type="rl" or part_type="jc" or part_type="dq" or part_type="zh" or part_type="tpxw") ORDER BY id DESC' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)
        except Exception, e:
            pass
    # list = session.get('list')
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == '4':
        partname = '规章制度'
    else:
        pass
    return render_template('list.html', list=list, partname=partname)


@app.route('/dq_list/<type>')
def dq_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []

    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "dq" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "dq" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    # session['list'] = list
    if type == '1':
        partname = '组织工作'
    elif type == '2':
        partname = '宣传工作'
    elif type == '3':
        partname = '纪检工作'
    elif type == '4':
        partname = '工会工作'
    elif type == '5':
        partname = '团青工作'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('dq_list.html', list=list, partname=partname)


@app.route('/zh_list/<type>')
def zh_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "zh" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "zh" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('zh_list.html', list=list, partname=partname)


@app.route('/yx_list/<type>')
def yx_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "yx" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "yx" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '走访情况'
    elif type == '3':
        partname = '市场变化'
    elif type == '4':
        partname = '新增货源'
    elif type == '5':
        partname = '营销文电'
    else:
        pass
    return render_template('yx_list.html', list=list, partname=partname)


@app.route('/rl_list/<type>')
def rl_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "rl" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "rl" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '人事管理'
    elif type == '3':
        partname = '工资定额'
    elif type == '4':
        partname = '规章制度'
    elif type == '5':
        partname = '社保年金'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('rl_list.html', list=list, partname=partname)


@app.route('/jc_list/<type>')
def jc_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "jc" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "jc" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '收入文电'
    elif type == '3':
        partname = '财务文电'
    elif type == '4':
        partname = '常用资料'
    elif type == '5':
        partname = '税务资料'
    elif type == '6':
        partname = '收入情况'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('jc_list.html', list=list, partname=partname)


@app.route('/ja_list/<type>')
def ja_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
    slen = select_list.__len__()
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "ja" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "ja" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '安全管理'
    elif type == '3':
        partname = '货运业务'
    elif type == '4':
        partname = '规章制度'
    elif type == '5':
        partname = '基建管理'
    elif type == '6':
        partname = '电算工作'
    elif type == '7':
        partname = '信息共享'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('ja_list.html', list=list, partname=partname, select_list=select_list, slen=slen)


@app.route('/zx_list/<type>')
def zx_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "zx" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "zx" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '物资管理'
    elif type == '3':
        partname = '设备运用'
    elif type == '4':
        partname = '规章制度'
    elif type == '5':
        partname = '经验交流'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('zx_list.html', list=list, partname=partname)


@app.route('/zj_list/<type>')
def zj_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "zj" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "zj" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '职教文件'
    elif type == '3':
        partname = '培训考试'
    elif type == '4':
        partname = '学习计划'
    elif type == '5':
        partname = '岗位练兵'
    elif type == '6':
        partname = '培训课件'
    elif type == '7':
        partname = '职教台账'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('zj_list.html', list=list, partname=partname)


@app.route('/dd_list/<type>')
def dd_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "dd" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "dd" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '调度命令'
    elif type == '3':
        partname = '生产指标'
    elif type == '4':
        partname = '生产资料'
    elif type == '5':
        partname = '应急预案'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('dd_list.html', list=list, partname=partname)


@app.route('/gt_list/<type>')
def gt_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "gt" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "gt" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '调度命令'
    elif type == '3':
        partname = '生产指标'
    elif type == '4':
        partname = '生产资料'
    elif type == '5':
        partname = '应急预案'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('gt_list.html', list=list, partname=partname)


@app.route('/nnnyys_list/<type>')
def nnnyys_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "nnnyys" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "nnnyys" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('nnnyys_list.html', list=list, partname=partname)


@app.route('/kys_list/<type>')
def kys_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "kys" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "kys" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('kys_list.html', list=list, partname=partname)


@app.route('/ggyys_list/<type>')
def ggyys_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "ggyys" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "ggyys" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('ggyys_list.html', list=list, partname=partname)


@app.route('/lbyys_list/<type>')
def lbyys_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "lbyys" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "lbyys" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('lbyys_list.html', list=list, partname=partname)


@app.route('/czyys_list/<type>')
def czyys_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "czyys" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "czyys" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('czyys_list.html', list=list, partname=partname)


@app.route('/nnnzgs_list/<type>')
def nnnzgs_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "nnnzgs" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "nnnzgs" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('nnnzgs_list.html', list=list, partname=partname)


@app.route('/ggzgs_list/<type>')
def ggzgs_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "ggzgs" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "ggzgs" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '教育培训'
    elif type == '3':
        partname = '安全、生产'
    elif type == '4':
        partname = '规章制度'
    elif type == '5':
        partname = '设备动态'
    elif type == '6':
        partname = '职工风采'
    elif type == '7':
        partname = '党建工会'
    elif type == 'new':
        partname = '最新动态'
    else:
        pass
    return render_template('ggzgs_list.html', list=list, partname=partname)


@app.route('/ltzgs_list/<type>')
def ltzgs_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "ltzgs" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "ltzgs" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('ltzgs_list.html', list=list, partname=partname)


@app.route('/czzgs_list/<type>')
def czzgs_list(type):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    if type == 'new':
        try:
            cursor.execute('select * from ja WHERE  part_type = "czzgs" ORDER BY id DESC ')
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
    else:
        try:
            cursor.execute('select * from ja WHERE type ="%s" AND part_type = "czzgs" ORDER BY id DESC ' % type)
            rows = cursor.fetchall()
            for row in rows:
                list.append(row)

        except Exception, e:
            pass
            # session['list'] = list
    if type == '1':
        partname = '通知公告'
    elif type == '2':
        partname = '经验交流'
    elif type == '3':
        partname = '生活动态'
    elif type == 'new':
        partname = '最新动态'
    else:
        partname = '规章制度'
    return render_template('czzgs_list.html', list=list, partname=partname)


def gen_rnd_filename():
    filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    return '%s%s' % (filename_prefix, str(random.randrange(1000, 10000)))


@app.route('/test', methods=['POST', 'GET'])
def test():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    # users = []
    # try:
    #     cursor.execute('select * from users where level = "3" order by ranking ')
    #     users = cursor.fetchall()
    # except:
    #     pass
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 获取当前时间
    today = datetime.datetime.strptime(str(date), '%Y-%m-%d')
    delta = datetime.timedelta(days=1)
    yesr = today - delta
    yesrtoday = str(yesr)
    tomouse = time.strftime('%m', time.localtime(time.time()))  # 获取当前时间
    toyear = time.strftime('%Y', time.localtime(time.time()))  # 获取当前时间
    safedays = 1
    ydays = 365
    try:
        cursor.execute('select days from safedays where type = "1" order by id desc limit 1')
        d = cursor.fetchone()
        safedays = (today - datetime.datetime.strptime(d[0], '%Y-%m-%d')).days

        year = int(toyear)
        start = toyear + '-01-01'
        cdays = (today - datetime.datetime.strptime(start, '%Y-%m-%d')).days  # 已完成days天

        # 判断是否为闰年
        if (year % 4 == 0) & (year % 100 != 0):
            ydays = 366
        elif year % 400 == 0:
            ydays = 366
        else:
            ydays = 365

    except Exception:
        pass
    years = toyear + '%'
    jd1 = 0
    jd2 = 0
    cqjd1 = 0
    plan1 = 0
    plan2 = 0
    cqjd2 = 0
    try:
        cursor.execute("SELECT complete FROM charts where chart_type = '1' and date like '%s' " % years)
        complete1 = cursor.fetchall()
        cursor.execute(
            'select days from safedays where type = "2" and year = "%s" order by id desc limit 1' % toyear)  # 年度任务
        p1 = cursor.fetchone()
        cp1 = 0
        plan1 = p1[0]
        for c1 in complete1:
            cp1 = cp1 + float(c1[0])
        jd1 = round((float(cp1) / float(plan1)) * 100, 2)

        cqjd1 = round((float(cp1) - (float(plan1) * cdays) / ydays) / float(plan1) * 100, 2)

    except Exception, e:
        pass
    try:
        cursor.execute("SELECT complete FROM charts where chart_type = '2' and date like '%s' " % years)
        complete2 = cursor.fetchall()
        cursor.execute('select days from safedays where type = "3" order by id desc limit 1')
        p2 = cursor.fetchone()
        cp2 = 0
        plan2 = p2[0]
        for c2 in complete2:
            cp2 = cp2 + float(c2[0])
        jd2 = round((float(cp2) / float(plan2)) * 100, 2)
        cqjd2 = round((float(cp2) - (float(plan2) * cdays) / ydays) / float(plan2) * 100, 2)
    except Exception, e:
        pass

    test3 = 0
    cursor.execute("SELECT complete FROM charts where chart_type = '1' and id = '%s' " % 455)
    ttt = cursor.fetchone()
    test3 = float(ttt[0])
    return render_template('test.html', cp1=cp1, jd1=jd1, cp2=cp2, jd2=jd2, plan1=plan1, plan2=plan2, cqjd1=cqjd1,
                           cqjd2=cqjd2,test3=test3)


@app.route('/choice', methods=['POST', 'GET'])
def choice():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    users = []
    try:
        cursor.execute('select * from users where level = "3" ')
        users = cursor.fetchall()
    except:
        pass
    testname = []
    name = ''
    if request.method == 'POST':
        testname = request.values.getlist('testname')
        return testname
    return render_template('choice.html', users=users)


# @app.route('/test', methods=['POST', 'get'])
# def test():
#     conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
#     cursor = conn.cursor()
#     id = 108
#     cursor.execute('select part_type,filename,filepath from ja where id = "%s"' % (id))
#     row = cursor.fetchone()
#
#     document = Document()
#     ss = document.StyleSheet
#     document.add_heading('adbc', 0)
#
#
#     p = document.add_paragraph('A plain paragraph having some ')
#     p.add_run('bold').bold = True
#     p.add_run(' and some ')
#     p.add_run('italic.').italic = True
#
#     document.add_heading('Heading, level 1', level=1)
#     document.add_paragraph('Intense quote', style='IntenseQuote')
#
#     document.add_paragraph(
#         'first item in unordered list', style='ListBullet'
#     )
#     document.add_paragraph(
#         'first item in ordered list', style='ListNumber'
#     )
#     table = document.add_table(rows=1, cols=4)
#     # table.style.font.name = u'宋体'
#     hdr_cells = table.rows[0].cells
#     hdr_cells[0].text = '来文日期'
#     hdr_cells[1].text = '2018-3-28'
#     hdr_cells[2].text = '编号'
#     hdr_cells[3].text = '1'
#     #
#     # row_cells = table.add_row().cells
#     # row_cells[0].text = '公文文号'
#     # row_cells[1].text = '1'
#     # row_cells[2].text = '紧急程度'
#     # row_cells[2].text = '平件'
#     #
#     # row_cells = table.add_row().cells
#     # row_cells[0].text = '标题'
#     # row_cells[1].text = '呵呵呵呵'
#     # row_cells[2].text = '1'
#     # row_cells[2].text = '1'
#     #
#     # row_cells = table.add_row().cells
#     # row_cells[0].text = '拟办意见'
#     # row_cells[1].text = '哈哈哈'
#     # row_cells[2].text = '1'
#     # row_cells[2].text = '1'
#
#     document.add_page_break()
#     name = 'demo.docx'
#     basepath = os.path.dirname(__file__)
#     upload_path1 = os.path.join(basepath, 'static/docx', name)
#     document.save(upload_path1)
#     return render_template('test.html', p=id)


@app.route('/ckupload/', methods=['POST', 'OPTIONS'])
def ckupload():
    """CKEditor file upload"""
    error = ''
    url = ''
    callback = request.args.get("CKEditorFuncNum")

    if request.method == 'POST' and 'upload' in request.files:
        fileobj = request.files['upload']
        fname, fext = os.path.splitext(fileobj.filename)
        rnd_name = '%s%s' % (gen_rnd_filename(), fext)

        filepath = os.path.join(app.static_folder, 'upload', rnd_name)

        # 检查路径是否存在，不存在则创建
        dirname = os.path.dirname(filepath)
        if not os.path.exists(dirname):
            try:
                os.makedirs(dirname)
            except:
                error = 'ERROR_CREATE_DIR'
        elif not os.access(dirname, os.W_OK):
            error = 'ERROR_DIR_NOT_WRITEABLE'

        if not error:
            fileobj.save(filepath)
            url = url_for('static', filename='%s/%s' % ('upload', rnd_name))
    else:
        error = 'post error'

    res = """<script type="text/javascript">
  window.parent.CKEDITOR.tools.callFunction(%s, '%s', '%s');
</script>""" % (callback, url, error)

    response = make_response(res)
    response.headers["Content-Type"] = "text/html"
    return response


@app.route('/mark_input', methods=['POST', 'get'])
def mark_input():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 获取当前时间
    title = ''
    filename = ''
    filepath = ''
    number = ''
    text_type = ''
    family = ''
    degree = ''
    markman = ''
    opinion = ''
    points = ''
    remark = ''
    content = ''
    filepaths = ''
    filenames = ''
    complete = ''
    row = []
    try:
        if request.method == 'POST':

            title = request.values.get('title')
            content = request.values.get('content')
            text_type = request.values.get('text_type')
            number = request.values.get('number')
            family = request.values.get('family')
            degree = request.values.get('degree')
            markmans = request.values.getlist('markman')
            opinion = request.values.get('opinion')
            points = request.values.get('points')
            remark = request.values.get('remark')
            receipt = request.values.get('receipt')
            complete = request.values.get('complete')

            for m in markmans:
                markman = markman + m + ';'

            filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt2 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt3 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt4 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt5 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt1)
            rnd2 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt2)
            rnd3 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt3)
            rnd4 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt4)
            rnd5 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt5)

            try:
                basepath = os.path.dirname(__file__)
                file = request.files['file']
                filename = file.filename
                if filename.__len__() > 0:
                    fname, fext = os.path.splitext(filename)
                    rnd_name1 = '%s%s' % (rnd1, fext)
                    upload_path1 = os.path.join(basepath, 'static\uploads', rnd_name1)
                    file.save(upload_path1)
                    filepaths = rnd_name1
                    filenames = filename
            except Exception:
                pass

            try:
                file2 = request.files['file2']
                filename2 = file2.filename
                if filename2.__len__() > 0:
                    fname, fext = os.path.splitext(filename2)
                    rnd_name2 = '%s%s' % (rnd2, fext)
                    upload_path2 = os.path.join(basepath, 'static\uploads', rnd_name2)
                    file2.save(upload_path2)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name2
                        filenames = filenames + filename2
                    else:
                        filepaths = filepaths + ';' + rnd_name2
                        filenames = filenames + ';' + filename2
            except Exception:
                pass

            try:
                file3 = request.files['file3']
                filename3 = file3.filename
                if filename3.__len__() > 0:
                    fname, fext = os.path.splitext(filename3)
                    rnd_name3 = '%s%s' % (rnd3, fext)
                    upload_path3 = os.path.join(basepath, 'static\uploads', rnd_name3)
                    file3.save(upload_path3)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name3
                        filenames = filenames + filename3
                    else:
                        filepaths = filepaths + ';' + rnd_name3
                        filenames = filenames + ';' + filename3
            except Exception:
                pass

            try:
                file4 = request.files['file4']
                filename4 = file4.filename
                if filename4.__len__() > 0:
                    fname, fext = os.path.splitext(filename4)
                    rnd_name4 = '%s%s' % (rnd4, fext)
                    upload_path4 = os.path.join(basepath, 'static\uploads', rnd_name4)
                    file4.save(upload_path4)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name4
                        filenames = filenames + filename4
                    else:
                        filepaths = filepaths + ';' + rnd_name4
                        filenames = filenames + ';' + filename4
            except Exception:
                pass

            try:
                file5 = request.files['file5']
                filename5 = file5.filename
                if filename5.__len__() > 0:
                    fname, fext = os.path.splitext(filename5)
                    rnd_name5 = '%s%s' % (rnd5, fext)
                    upload_path5 = os.path.join(basepath, 'static\uploads', rnd_name5)
                    file5.save(upload_path5)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name5
                        filenames = filenames + filename5
                    else:
                        filepaths = filepaths + ';' + rnd_name5
                        filenames = filenames + ';' + filename5
            except Exception:
                pass
    except Exception:
        pass
    success = ''
    if title.__len__() > 0:
        try:
            cursor.execute(
                "insert into mark(title,content,filename,filepath,number,text_type,family,degree,markman,opinion,points,remark,time,completetime)"
                " value('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" %
                (title, content, filenames, filepaths, number, text_type, family, degree, markman, opinion, points,
                 remark,receipt,complete))
        except Exception:
            success = 'no'

    else:
        pass
    if success != 'no':
        try:
            cursor.execute('select id from mark order by id desc limit 1')  # 获取最新上传信息的id
            mid = cursor.fetchone()
            n = cursor.execute('select mid from markhandle where mid = "%s" ' % (mid[0]))  # 判断该消息是否录入批阅处理表
            if n == 0:
                for m in markmans:
                    cursor.execute(
                        'insert into markhandle(mid,markman,marked,addman,handle,handletime,completetime) value("%s","%s","n","%s","","","%s")' % (
                            mid[0], m, nickname,complete))
                conn.commit()
                return redirect(url_for('mark_alllist'))
            else:
                pass
        except Exception:
            pass
    users = []
    try:
        cursor.execute('select * from users where level = "8" order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('mark_input.html', nickname=nickname, users=users, row=row,today = date)


@app.route('/mark_adminedit/<id>', methods=['POST', 'get'])
def mark_adminedit(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    markman = ''
    nickname = ''
    hrow = ''
    hlist = []
    edit = 'y'
    markman = session.get('username')
    nickname = session.get('nickname')
    level = session.get('level')
    session['id3'] = id
    try:
        cursor.execute('select * from mark where id = "%s" ' % id)
        row = cursor.fetchone()
    except Exception:
        pass
    users = []
    try:
        cursor.execute('select * from users  order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass

    filed = []
    filed = row[2].split(';')
    flen = filed.__len__()
    filepath = row[3].split(';')
    session['filepath2'] = filepath
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('mark_adminedit.html', row=row, flen=flen, filed=filed, nickname=nickname, users=users,
                           edit=edit)


@app.route('/markedit_submit', methods=['POST', 'get'])
def markedit_submit():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    id = ''
    part_type = ''
    nickname = ''
    id = session.get('id3')
    # part_type = session.get('part_type')
    # user_type = session.get('user_type')
    nickname = session.get('nickname')
    content = ''
    filename = ''
    filename2 = ''
    filename3 = ''
    filename4 = ''
    filename5 = ''
    filenames = ''
    filepaths = ''
    filepathed = ''
    markman = ''
    type = ''
    sign_type = ''
    fileds = []
    filed = ''
    complete=''
    date = time.strftime('%Y-%m-%d', time.localtime(time.time()))

    try:
        if request.method == 'POST':

            title = request.values.get('title')
            text_type = request.values.get('text_type')
            number = request.values.get('number')
            family = request.values.get('family')
            degree = request.values.get('degree')
            markmans = request.values.getlist('markman')
            opinion = request.values.get('opinion')
            points = request.values.get('points')
            remark = request.values.get('remark')
            complete = request.values.get('complete')

            fileds = request.values.getlist('filed')
            filepath = session.get('filepath2')
            for i in range(0, fileds.__len__()):
                if fileds[i].__len__() > 0:
                    filed = filed + fileds[i] + ';'
                    filepathed = filepathed + filepath[i] + ';'
            for m in markmans:
                markman = markman + m + ';'

            filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt2 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt3 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt4 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            salt5 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
            rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt1)
            rnd2 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt2)
            rnd3 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt3)
            rnd4 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt4)
            rnd5 = '%s%s%s' % (filename_prefix, str(random.randrange(1000, 1000000)), salt5)

            try:
                basepath = os.path.dirname(__file__)
                file = request.files['file']
                filename = file.filename
                if filename.__len__() > 0:
                    fname, fext = os.path.splitext(filename)
                    rnd_name1 = '%s%s' % (rnd1, fext)
                    upload_path1 = os.path.join(basepath, 'static\uploads', rnd_name1)
                    file.save(upload_path1)
                    filepaths = rnd_name1
                    filenames = filename
            except Exception:
                pass

            try:
                file2 = request.files['file2']
                filename2 = file2.filename
                if filename2.__len__() > 0:
                    fname, fext = os.path.splitext(filename2)
                    rnd_name2 = '%s%s' % (rnd2, fext)
                    upload_path2 = os.path.join(basepath, 'static\uploads', rnd_name2)
                    file2.save(upload_path2)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name2
                        filenames = filenames + filename2
                    else:
                        filepaths = filepaths + ';' + rnd_name2
                        filenames = filenames + ';' + filename2

            except Exception:
                pass

            try:
                file3 = request.files['file3']
                filename3 = file3.filename
                if filename3.__len__() > 0:
                    fname, fext = os.path.splitext(filename3)
                    rnd_name3 = '%s%s' % (rnd3, fext)
                    upload_path3 = os.path.join(basepath, 'static\uploads', rnd_name3)
                    file3.save(upload_path3)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name3
                        filenames = filenames + filename3
                    else:
                        filepaths = filepaths + ';' + rnd_name3
                        filenames = filenames + ';' + filename3
            except Exception:
                pass

            try:
                file4 = request.files['file4']
                filename4 = file4.filename
                if filename4.__len__() > 0:
                    fname, fext = os.path.splitext(filename4)
                    rnd_name4 = '%s%s' % (rnd4, fext)
                    upload_path4 = os.path.join(basepath, 'static\uploads', rnd_name4)
                    file4.save(upload_path4)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name4
                        filenames = filenames + filename4
                    else:
                        filepaths = filepaths + ';' + rnd_name4
                        filenames = filenames + ';' + filename4
            except Exception:
                pass

            try:
                file5 = request.files['file5']
                filename5 = file5.filename
                if filename5.__len__() > 0:
                    fname, fext = os.path.splitext(filename5)
                    rnd_name5 = '%s%s' % (rnd5, fext)
                    upload_path5 = os.path.join(basepath, 'static\uploads', rnd_name5)
                    file5.save(upload_path5)
                    if filepaths == '':
                        filepaths = filepaths + rnd_name5
                        filenames = filenames + filename5
                    else:
                        filepaths = filepaths + ';' + rnd_name5
                        filenames = filenames + ';' + filename5
            except Exception:
                pass
            filenames = filed + filenames
            filepaths = filepathed + filepaths

    except Exception:
        pass

    success = ''

    if title.__len__() > 0:
        try:
            cursor.execute(
                'update mark set title = "%s",filename = "%s",filepath="%s",number="%s",text_type="%s",family="%s",'
                'degree="%s",markman="%s",opinion="%s",points="%s",remark="%s",completetime="%s" WHERE id="%s"' % (
                    title, filenames, filepaths, number, text_type, family, degree, markman, opinion, points, remark,complete,
                    id))
            conn.commit()
        except Exception, e:
            success = 'no'
    else:
        pass

    man = ''

    if success != 'no':
        try:
            for m in markmans:
                man = cursor.execute('select id from markhandle where mid = "%s" and markman ="%s"' % (id, m))
                if man == 0:
                    cursor.execute(
                        'insert into markhandle(mid,markman,marked,addman,handle,handletime,completetime) value("%s","%s","n","%s","","","%s")' % (
                            id, m, nickname,complete))
                    conn.commit()

            return redirect(url_for('mark_alllist'))
        except Exception:
            pass

    conn.commit()
    conn.close()

    return redirect(url_for('mark_alllist'))


@app.route('/marking/<id>', methods=['POST', 'get'])
def marking(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 获取当前时间
    markman = ''
    hrow = ''
    hlist = []
    state = 'y'
    markman = session.get('username')
    nickname = session.get('nickname')
    level = session.get('level')
    filename = []
    filepath = []
    try:
        cursor.execute('select * from mark where id = "%s" ' % id)
        row = cursor.fetchone()
        # session['content'] = row[12]
        filename = row[2].split(';')
        filepath = row[3].split(';')
    except Exception:
        pass
    try:
        cursor.execute('select * from markhandle where mid = "%s" and markman = "%s"' % (id, markman))
        hrow = cursor.fetchone()
        state = hrow[3]
    except Exception:
        pass
    handle = ''
    part_type = ''
    leader = ''
    leadpart = ''
    leadpartname = ''
    try:
        handle = request.values.get('handle')
        parts = request.values.getlist('markman')
        leader = request.values.get('leader')
        leadpart = request.values.get('leadpart')
        for p in parts:
            part_type = part_type + p + ';'
    except Exception:
        pass
    n = 0
    try:
        if leader.__len__() > 0:
            handle = handle + ' 牵头领导为:' + leader + '。请其他领导阅知。'
            n = cursor.execute('select id from markhandle where mid = "%s" and markman = "%s"' % (id, leader))
            if n == 0:
                cursor.execute('insert into markhandle(mid,markman,marked,addman,handle,handletime,completetime) '
                               'value("%s","%s","n","%s","","","%s") ' % (id, leader, nickname,hrow[10]))
            else:
                pass

        if leadpart.__len__() > 0:
            try:
                cursor.execute('select nickname from users where username = "%s" ' % (leadpart))
                lrow = cursor.fetchone()
                leadpartname = lrow[0]
            except Exception:
                pass
            handle = handle + ' 牵头部门为:' + leadpartname + '。'
            n = cursor.execute('select id from markhandle where mid = "%s" and markman = "%s"' % (id, leadpart))
            if n == 0:
                cursor.execute('insert into markhandle(mid,markman,marked,addman,handle,handletime,completetime) '
                               'value("%s","%s","n","%s","","","%s") ' % (id, leadpart, nickname,hrow[10]))
            else:
                pass

        if handle.__len__() > 0:
            cursor.execute(
                'update markhandle set handle = "%s" , part_type="%s" ,marked="y",handleman="%s",handletime="%s"  '
                'where mid= "%s"and markman= "%s"' % (handle, part_type, nickname, date, id, markman))
            if level == '8':

                for p in parts:
                    n = cursor.execute('select id from markhandle where mid = "%s" and markman = "%s"' % (id, p))
                    if n == 0:
                        cursor.execute('insert into markhandle(mid,markman,marked,addman,handle,handletime,completetime) '
                                       'value("%s","%s","n","%s","","","%s") ' % (id, p, nickname,hrow[10]))
                    else:
                        pass
            conn.commit()
            return redirect(url_for('mark_list', state='n'))
    except Exception:
        pass
    markedman = []
    try:
        # cursor.execute('select * from markhandle where mid = "%s" order by id' % (id))
        cursor.execute('select a.id,a.handle,a.addman,a.handletime,a.color,b.nickname,b.level from markhandle a left join '
                       'users b on a.markman=b.username where mid = "%s" order by a.id' % id)
        hlist = cursor.fetchall()
    except Exception:
        pass
    users = []
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    words = []
    try:
        cursor.execute('select * from commonword order by id desc limit 8')
        words = cursor.fetchall()
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return render_template('marking.html', filename=filename, filepath=filepath, hlist=hlist, row=row, hrow=hrow,
                           words=words,
                           nickname=nickname, state=state, level=level, users=users, markedman=markedman)


@app.route('/mark_edit/<id>', methods=['get', 'post'])
def mark_edit(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    markman = ''
    hrow = ''
    hlist = []
    state = 'n'
    markman = session.get('username')
    nickname = session.get('nickname')
    level = session.get('level')
    try:
        cursor.execute('select * from mark where id = "%s" ' % id)
        row = cursor.fetchone()
    except Exception:
        pass
    try:
        cursor.execute('select * from markhandle where mid = "%s" and markman = "%s"' % (id, markman))
        hrow = cursor.fetchone()
    except Exception:
        pass
    names = []
    markedman = []
    try:
        cursor.execute(
            'select a.id,a.handle,a.addman,a.handletime,a.color,b.nickname,b.level from markhandle a left join '
            'users b on a.markman=b.username where mid = "%s" order by a.id' % id)
        hlist = cursor.fetchall()
        # cursor.execute('select * from markhandle where mid = "%s" ' % (id))
        # hlist = cursor.fetchall()
        # for h in hlist:
        #     markedman.append(h[2])
        #     cursor.execute('select nickname from users where username = "%s"' % (h[2]))
        #     name = cursor.fetchone()
        #     names.append(name[0])
    except Exception:
        pass
    users = []
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    words = []
    try:
        cursor.execute('select * from commonword order by id desc limit 8')
        words = cursor.fetchall()
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('marking.html', hlist=hlist, row=row, hrow=hrow, nickname=nickname, state=state, level=level,
                           users=users, names=names, markedman=markedman, words=words)


@app.route('/delete_handle/<id>', methods=['get', 'post'])
def delete_handle(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    level = session.get('level')
    username = session.get('username')
    row = []
    markmans = []
    markman = ''
    try:
        cursor.execute('select mid,markman from markhandle where id = "%s" limit 1' % (id))
        row = cursor.fetchone()
    except Exception, e:
        print e

    if level == '2':
        try:
            cursor.execute('select * from mark where id = "%s" limit 1' % (row[0]))
            mark = cursor.fetchone()
            markmans = mark[8].split(';')
            for m in markmans:
                if m == row[1]:
                    pass
                else:
                    markman = markman + m + ';'
        except Exception, e:
            print e
        try:
            cursor.execute('update mark set markman = "%s" where id = "%s"' % (markman, row[0]))
        except Exception:
            pass
    else:

        try:
            cursor.execute('select * from markhandle where mid = "%s" and markman ="%s"' % (row[0], username))
            mark = cursor.fetchone()
            markmans = mark[5].split(';')
            for m in markmans:
                if m == row[1]:
                    pass
                else:
                    markman = markman + m + ';'
        except Exception, e:
            print e

        try:
            cursor.execute(
                'update markhandle set part_type = "%s" where mid = "%s" and markman ="%s"' % (
                    markman, row[0], username))
        except Exception:
            pass

    try:
        cursor.execute('delete from markhandle where id = "%s"' % (id))
        conn.commit()
    except Exception, e:
        print e

    if level == '8':
        return redirect(url_for('mark_edit', id=row[0]))
    elif level == '2':
        return redirect(url_for('marking', id=row[0]))
    return redirect(url_for('markhandle_edit', id=row[0]))


@app.route('/mark_handle/<id>', methods=['get', 'post'])
def mark_handle(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 获取当前时间
    user_type = ''
    state = ''
    hrow = ''
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    username = session.get('username')
    level = session.get('level')
    filename = []
    filepath = []
    try:
        cursor.execute('select * from mark where id = "%s" ' % id)
        row = cursor.fetchone()
        filename = row[2].split(';')
        filepath = row[3].split(';')
    except Exception:
        pass
    try:
        cursor.execute('select * from markhandle where mid = "%s" and markman = "%s"' % (id, username))
        hrow = cursor.fetchone()
        state = hrow[3]
    except Exception:
        pass
    handle = ''
    part_type = ''
    try:
        handle = request.values.get('handle')
        parts = request.values.getlist('markman')
        for p in parts:
            part_type = part_type + p + ';'
    except Exception:
        pass

    try:
        if handle.__len__() > 0:
            cursor.execute(
                'update markhandle set handle = "%s" , part_type="%s" ,marked="y",handleman="%s",handletime="%s" '
                'where mid= "%s"and markman= "%s"' % (handle, part_type, nickname, date, id, username))
            for p in parts:
                n = cursor.execute('select * from markhandle where mid = "%s" and markman = "%s"' % (id, p))
                if n == 0:
                    cursor.execute('insert into markhandle(mid,markman,marked,addman,handle,handletime,completetime) '
                                   'value("%s","%s","n","%s","","","%s") ' % (id, p, nickname,hrow[10]))
                else:
                    pass
            conn.commit()
            return redirect(url_for('mark_partlist', state='n'))
    except Exception:
        pass
    markedman = []
    try:
        # cursor.execute('select * from markhandle where mid = "%s" order by id' % (id))
        cursor.execute(
            'select a.id,a.handle,a.addman,a.handletime,a.color,b.nickname,b.level from markhandle a left join '
            'users b on a.markman=b.username where mid = "%s" order by a.id' % id)
        hlist = cursor.fetchall()

    except Exception:
        pass
    users = []
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    words = []
    try:
        cursor.execute('select * from commonword order by id desc limit 8')
        words = cursor.fetchall()
    except Exception:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return render_template('mark_handle.html', filename=filename, filepath=filepath, users=users, row=row, hrow=hrow,
                           nickname=nickname, hlist=hlist, state=state, markedman=markedman,
                           user_type=user_type,
                           level=level, words=words)


@app.route('/markhandle_edit/<id>', methods=['get', 'post'])
def markhandle_edit(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    user_type = ''
    hrow = ''
    state = 'n'
    username = session.get('username')
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    level = session.get('level')
    try:
        cursor.execute('select * from mark where id = "%s" ' % id)
        row = cursor.fetchone()
    except Exception:
        pass
    try:
        cursor.execute('select * from markhandle where mid = "%s" and markman = "%s"' % (id, username))
        hrow = cursor.fetchone()
    except Exception:
        pass
    names = []
    markedman = []
    try:
        cursor.execute(
            'select a.id,a.handle,a.addman,a.handletime,a.color,b.nickname,b.level from markhandle a left join '
            'users b on a.markman=b.username where mid = "%s" order by a.id' % id)
        hlist = cursor.fetchall()
        # cursor.execute('select * from markhandle where mid = "%s"' % (id))
        # hlist = cursor.fetchall()
        # for h in hlist:
        #     markedman.append(h[2])
        #     cursor.execute('select nickname from users where username = "%s"' % (h[2]))
        #     name = cursor.fetchone()
        #     names.append(name[0])
    except Exception:
        pass
    users = []
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    words = []
    try:
        cursor.execute('select * from commonword order by id desc limit 8')
        words = cursor.fetchall()
    except Exception:
        pass
    # conn.commit()
    cursor.close()
    conn.close()
    return render_template('mark_handle.html', user_type=user_type, hrow=hrow, row=row, nickname=nickname, hlist=hlist,
                           state=state, names=names, markedman=markedman, users=users, level=level, words=words)


@app.route('/mark_query', methods=['get', 'post'])
def mark_query():
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    titletext = ''
    family = ''
    list = []
    text_type = ''
    degree = ''
    try:
        titletext = request.values.get('titletext')  # 获取搜索文本
        family = request.values.get('family')
        text_type = request.values.get('text_type')
        degree = request.values.get('degree')
    except Exception:
        pass
    try:
        str = '%' + titletext + '%'  # 模糊查询
        strfamliy = '%' + family + '%'
        strtext_type = '%' + text_type + '%'
        strdegree = '%' + degree + '%'
        if titletext == '标题关键字':
            cursor.execute(
                'select * from mark where family like "%s" and text_type like "%s" and degree like "%s" '
                'ORDER BY id DESC' % (strfamliy, strtext_type, strdegree))
        else:
            # cursor.execute('select * from mark where title like "%s" and family like "%s" ORDER BY id DESC' % (str,strfamliy))  # like模糊查询
            cursor.execute(
                'select * from mark where title like "%s" and family like "%s" and text_type like "%s" and degree like "%s" '
                'ORDER BY id DESC' % (str, strfamliy, strtext_type, strdegree))
        rows = cursor.fetchall()
        for row in rows:
            list.append(row)
    except Exception:
        pass

    nolist = []

    try:
        for l in list:
            nohandle = cursor.execute('select id from markhandle where mid="%s" and marked="n"' % l[0])
            nolist.append(nohandle)
    except Exception:
        pass
    return render_template('mark_alllist.html', list=list,nolist=nolist)


@app.route('/mark_alllist')
def mark_alllist():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    markman = ''
    list = []
    mids = []
    markman = session.get('username')
    nickname = session.get('nickname')
    n = 0
    nohandle = 0
    nolist=[]
    try:
        cursor.execute('select * from mark order by id desc')
        list = cursor.fetchall()
    except Exception:
        pass
    try:
        for l in list:
            nohandle = cursor.execute('select id from markhandle where mid="%s" and marked="n"' % l[0])
            nolist.append(nohandle)
    except Exception:
        pass
    return render_template('mark_alllist.html', list=list, nickname=nickname,nolist=nolist)


@app.route('/result/<id>', methods=['get', 'post'])
def result(id):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    row = []
    list = []
    lead = []
    part = []
    level = []
    try:
        cursor.execute('select * from mark where id = "%s"' % id)
        row = cursor.fetchone()
    except Exception:
        pass
    try:
        cursor.execute('select a.markman,a.handle,b.nickname from markhandle a left join '
                       'users b on a.markman=b.username where mid = "%s" and marked = "y"' % id)
        list = cursor.fetchall()
        for i in list:
            cursor.execute('select level from users where username = "%s" ' % i[0])
            level = cursor.fetchone()
            if level[0] == '8':
                lead.append(i)
            elif level[0] == '3' or level[0] == '4':
                part.append(i)

    except Exception:
        pass

    return render_template('result.html', row=row,lead=lead,part=part)


@app.route('/marked_query/<state>', methods=['get', 'post'])
def marked_query(state):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    titletext = ''
    family = ''
    list = []
    text_type = ''
    degree = ''
    markman = session.get('username')
    nickname = session.get('nickname')
    try:
        titletext = request.values.get('titletext')  # 获取搜索文本
        family = request.values.get('family')
        text_type = request.values.get('text_type')
        degree = request.values.get('degree')
    except Exception:
        pass
    try:
        str = '%' + titletext + '%'  # 模糊查询
        strfamliy = '%' + family + '%'
        strtext_type = '%' + text_type + '%'
        strdegree = '%' + degree + '%'
        if titletext == '标题关键字':
            cursor.execute(
                'select * from mark where family like "%s" and text_type like "%s" and degree like "%s" '
                'ORDER BY id DESC' % (strfamliy, strtext_type, strdegree))
        else:
            cursor.execute(
                'select * from mark where title like "%s" and family like "%s" and text_type like "%s" and degree like "%s" '
                'ORDER BY id DESC' % (str, strfamliy, strtext_type, strdegree))
        rows = cursor.fetchall()
        mids = []
        try:
            cursor.execute(
                'select mid from markhandle where markman = "%s" and marked = "%s" order by id desc' % (markman, state))
            mid = cursor.fetchall()
            for m in mid:
                mids.append(m[0])
        except Exception:
            pass
        for row in rows:
            if row[0] in mids:
                list.append(row)
    except Exception:
        pass

    return render_template('mark_list.html', list=list, nickname=nickname, state=state)


@app.route('/mark_list/<state>')
def mark_list(state):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    markman = ''
    list = []
    mids = []
    markman = session.get('username')
    nickname = session.get('nickname')
    n = 0
    try:
        cursor.execute(
            'select mid from markhandle where markman = "%s" and marked = "%s" order by id desc' % (markman, state))
        mids = cursor.fetchall()
        for mid in mids:
            cursor.execute('select * from mark where id = "%s"' % (mid))
            m = cursor.fetchone()
            list.append(m)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('mark_list.html', list=list, nickname=nickname, state=state)


@app.route('/markpart_query/<state>', methods=['get', 'post'])
def markpart_query(state):
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    titletext = ''
    family = ''
    list = []
    text_type = ''
    degree = ''
    markman = session.get('username')
    nickname = session.get('nickname')
    try:
        titletext = request.values.get('titletext')  # 获取搜索文本
        family = request.values.get('family')
        text_type = request.values.get('text_type')
        degree = request.values.get('degree')
    except Exception:
        pass
    try:
        str = '%' + titletext + '%'  # 模糊查询
        strfamliy = '%' + family + '%'
        strtext_type = '%' + text_type + '%'
        strdegree = '%' + degree + '%'
        if titletext == '标题关键字':
            cursor.execute(
                'select * from mark where family like "%s" and text_type like "%s" and degree like "%s" '
                'ORDER BY id DESC' % (strfamliy, strtext_type, strdegree))
        else:
            cursor.execute(
                'select * from mark where title like "%s" and family like "%s" and text_type like "%s" and degree like "%s" '
                'ORDER BY id DESC' % (str, strfamliy, strtext_type, strdegree))
        rows = cursor.fetchall()
        mids = []
        try:
            cursor.execute(
                'select mid from markhandle where markman = "%s" and marked = "%s" order by id desc' % (markman, state))
            mid = cursor.fetchall()
            for m in mid:
                mids.append(m[0])
        except Exception:
            pass
        for row in rows:
            if row[0] in mids:
                list.append(row)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('mark_partlist.html', list=list, nickname=nickname, state=state)


@app.route('/mark_partlist/<state>')
def mark_partlist(state):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    list = []
    partname = ''
    nickname = session.get('nickname')
    username = session.get('username')

    try:
        cursor.execute(
            'select mid from markhandle where markman = "%s" and marked = "%s" order by id desc' % (username, state))
        mids = cursor.fetchall()
        for mid in mids:
            cursor.execute('select * from mark where id = "%s"' % (mid))
            m = cursor.fetchone()
            list.append(m)
    except Exception:
        pass
    cursor.close()
    conn.close()
    return render_template('mark_partlist.html', list=list, nickname=nickname, state=state)


@app.route('/publish/<id>', methods=['get', 'post'])
def publish(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 获取当前时间
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    username = session.get('username')
    level = session.get('level')
    part_type = user_type
    if part_type == 'dq':
        partname = '党委办公室'  # 部门名称
        select_list = ['组织工作', '宣传工作', '纪检工作', '工会工作', '团青工作']  # 各部门导航条
        slen = select_list.__len__()
    elif part_type == 'zh':
        partname = '综合部'
        select_list = ['通知公告', '工作总结', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'yx':
        partname = '市场营销部'
        select_list = ['通知公告', '走访情况', '市场变化', '新增货源', '营销文电']
        slen = select_list.__len__()
    elif part_type == 'rl':
        partname = '人力资源部'
        select_list = ['通知公告', '人事管理', '工资定额', '规章制度', '社保年金']
        slen = select_list.__len__()
    elif part_type == 'jc':
        partname = '计划财务部'
        select_list = ['通知公告', '收入文电', '财务文电', '常用资料', '税务资料', '收入情况']
        slen = select_list.__len__()
    elif part_type == 'ja':
        partname = '技术安全部'
        select_list = ['通知公告', '安全管理', '货运业务', '规章制度', '基建管理', '电算工作', '信息共享']
        slen = select_list.__len__()
    elif part_type == 'zx':
        partname = '装卸管理部'
        select_list = ['通知公告', '物资管理', '设备运用', '规章制度', '经验交流']
        slen = select_list.__len__()
    elif part_type == 'zj':
        partname = '职工教育部'
        select_list = ['通知公告', '职教文件', '培训考试', '学习计划', '岗位练兵', '培训课件', '职教台账']
        slen = select_list.__len__()
    elif part_type == 'dd':
        partname = '生产调度室'
        select_list = ['通知公告', '调度命令', '生产指标', '生产资料', '应急预案']
        slen = select_list.__len__()
    elif part_type == 'kys':
        partname = '快运所'
        select_list = ['通知公告', '经验交流', '工作信息', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnyys':
        partname = '南宁南营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggyys':
        partname = '贵港营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']

        slen = select_list.__len__()
    elif part_type == 'lbyys':
        partname = '来宾营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czyys':
        partname = '崇左营业所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'nnnzgs':
        partname = '南宁南装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'ggzgs':
        partname = '贵港装管所'
        select_list = ['通知公告', '教育培训', '安全、生产', '规章制度', '设备动态', '职工风采', '党建工会']
        slen = select_list.__len__()
    elif part_type == 'ltzgs':
        partname = '黎塘装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    elif part_type == 'czzgs':
        partname = '崇左装管所'
        select_list = ['通知公告', '经验交流', '生活动态', '规章制度']
        slen = select_list.__len__()
    else:
        pass

    title = ''
    sign_type = ''
    text_tpye = ''
    filename = ''
    filepath = ''
    content = ''

    try:
        cursor.execute('select * from mark where id = "%s" ' % id)
        row = cursor.fetchone()
    except Exception:
        pass
    try:
        if request.method == 'POST':
            title = request.values.get('title')
            text_tpye = request.values.get('type')
            sign_types = request.values.getlist('sign_type')
            content = request.values.get('content')
        for s in sign_types:
            sign_type = sign_type + s + ';'
        filename = row[2]
        filepath = row[3]
    except Exception:
        pass

    try:
        if title.__len__() > 0:

            try:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname) value('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                        title, content, filename, date, text_tpye, part_type, sign_type, filepath, nickname))
                try:
                    cursor.execute('select handle from markhandle where mid = "%s" and markman = "%s"' % (id, username))
                    handle = cursor.fetchone()

                except Exception:
                    pass
                str = handle[0] + nickname + '已发布至网站。'
                try:
                    cursor.execute(
                        'update markhandle set handle = "%s" ,marked="y",color="red" where mid= "%s"and markman= "%s"' % (
                            str, id, username))
                except Exception:
                    pass

            except Exception:
                cursor.execute(
                    "insert into ja(title,content,filename,time,type,part_type,sign_type,filepath,nickname) value('%s','%s','','%s','%s','%s','%s','','%s')" % (
                        title, content, date, text_tpye, part_type, sign_type, nickname))
            conn.commit()
            try:
                cursor.execute('select id from ja order by id desc limit 1')
                jid = cursor.fetchone()
                ulen = cursor.execute('select username,nickname,user_type from users')
                us = cursor.fetchall()

                n = cursor.execute('select id from sign where id = "%s" ' % (jid[0]))
                if n == 0:
                    for i in range(0, ulen):
                        if us[i][0] in sign_types:
                            cursor.execute(
                                'insert into sign(id,username,nickname,state,user_type) value("%s","%s","%s","n","%s")' % (
                                    jid[0], us[i][0], us[i][1], us[i][2]))
                else:
                    pass
                conn.commit()
                return redirect(url_for('ja_display', id=jid[0]))
            except Exception:
                pass

        else:
            pass
    except Exception:
        pass

    users = []
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except Exception:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('publish.html', user_type=user_type, row=row, select_list=select_list, slen=slen,
                           nickname=nickname, part_type=part_type, level=level, users=users)

@app.route('/sendmail',methods=['get', 'post'])
def sendmail():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    user_type = session.get('user_type')
    nickname = session.get('nickname')
    username = session.get('username')
    level = session.get('level')
    state = 'send'
    receiver = ''
    receiverlist = []
    title = ''
    content = ''
    users = []
    filepaths = ''
    filenames = ''
    filed =[]
    try:
        cursor.execute('select * from users order by ranking')
        users = cursor.fetchall()
    except:
        pass

    if request.method =='POST':
        filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
        salt1 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
        salt2 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
        salt3 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
        salt4 = ''.join(random.sample(string.ascii_letters + string.digits, 4))
        rnd1 = '%s%s%s' % (filename_prefix, str(random.randrange(10, 10000)), salt1)
        rnd2 = '%s%s%s' % (filename_prefix, str(random.randrange(10, 10000)), salt2)
        rnd3 = '%s%s%s' % (filename_prefix, str(random.randrange(10, 10000)), salt3)
        rnd4 = '%s%s%s' % (filename_prefix, str(random.randrange(10, 10000)), salt4)

        try:
            basepath = os.path.dirname(__file__)
            file = request.files['file1']
            filename = file.filename
            if filename.__len__() > 0:
                fname, fext = os.path.splitext(filename)
                rnd_name1 = '%s%s' % (rnd1, fext)
                upload_path1 = os.path.join(basepath, 'static\uploads', rnd_name1)
                file.save(upload_path1)
                filepaths = rnd_name1
                filenames = filename
        except Exception:
            pass

        try:
            file2 = request.files['file2']
            filename2 = file2.filename
            if filename2.__len__() > 0:
                fname, fext = os.path.splitext(filename2)
                rnd_name2 = '%s%s' % (rnd2, fext)
                upload_path2 = os.path.join(basepath, 'static\uploads', rnd_name2)
                file2.save(upload_path2)
                if filepaths == '':
                    filepaths = filepaths + rnd_name2
                    filenames = filenames + filename2
                else:
                    filepaths = filepaths + ';' + rnd_name2
                    filenames = filenames + ';' + filename2
        except Exception:
            pass

        try:
            file3 = request.files['file3']
            filename3 = file3.filename
            if filename3.__len__() > 0:
                fname, fext = os.path.splitext(filename3)
                rnd_name3 = '%s%s' % (rnd3, fext)
                upload_path3 = os.path.join(basepath, 'static\uploads', rnd_name3)
                file3.save(upload_path3)
                if filepaths == '':
                    filepaths = filepaths + rnd_name3
                    filenames = filenames + filename3
                else:
                    filepaths = filepaths + ';' + rnd_name3
                    filenames = filenames + ';' + filename3
        except Exception:
            pass

        try:
            file4 = request.files['file4']
            filename4 = file4.filename
            if filename4.__len__() > 0:
                fname, fext = os.path.splitext(filename4)
                rnd_name4 = '%s%s' % (rnd4, fext)
                upload_path4 = os.path.join(basepath, 'static\uploads', rnd_name4)
                file4.save(upload_path4)
                if filepaths == '':
                    filepaths = filepaths + rnd_name4
                    filenames = filenames + filename4
                else:
                    filepaths = filepaths + ';' + rnd_name4
                    filenames = filenames + ';' + filename4
        except Exception:
            pass

        receiver = request.values.get('receiver')
        title = request.values.get('title')
        content1 = request.values.get('content')
        content = MySQLdb.escape_string(content1)
        receiverlist = receiver.split(';')
        filed = request.values.getlist('filed')
        mid = ''

        if filed.__len__() > 0:
            filepaths = session['mailfilepath']
            filenames = session['mailfilename']

        if receiver.__len__() > 0 and title.__len__()>0:
            try:
                cursor.execute('insert into mailinfo(receiver,sendman,nickname,title,filename,filepath,content,time) value("%s","%s","%s","%s","%s","%s","%s","%s")'
                               % (receiver,username,nickname,title,filenames,filepaths,content,date))
            except:
                pass

            try:
                cursor.execute('select mid from mailinfo order by mid desc limit 1')
                mid = cursor.fetchone()
            except:
                pass

            for r in range(0, receiverlist.__len__()):
                if receiverlist[r].__len__() > 0 :
                    try:
                        cursor.execute('insert into mail(receiver,sendman,nickname,title,filename,filepath,content,time,readed,mid,readtime) value("%s","%s","%s","%s","%s","%s","%s","%s","n","%s","")'
                                       % (receiverlist[r],username,nickname,title,filenames,filepaths,content,date,mid[0]))
                    except Exception:
                        pass
                else:
                    pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('sendmail.html',users=users,nickname=nickname,state=state)

@app.route('/inbox')
def inbox():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    nickname = session.get('nickname')
    username = session.get('username')
    list = []
    try:
        cursor.execute('select * from mail where receiver = "%s" order by id desc ' % username)
        list = cursor.fetchall()
    except Exception:
        pass
    cursor.close()
    conn.close()
    return  render_template('inbox.html',list=list,nickname=nickname)

@app.route('/unread')
def unread():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    nickname = session.get('nickname')
    username = session.get('username')
    list = []
    try:
        cursor.execute('select * from mail where receiver = "%s" and readed = "n" order by id desc' % username)
        list = cursor.fetchall()
    except Exception:
        pass
    cursor.close()
    conn.close()
    return  render_template('inbox.html',list=list,nickname=nickname)


@app.route('/sended')
def sended():
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    nickname = session.get('nickname')
    username = session.get('username')
    list = []
    allread = []
    readedlist = []
    try:
        # cursor.execute('select a.mid,a.receiver,a.sendman,a.title,a.time,a.readed,b.nickname from mailinfo a left join'
        #                ' users b on a.receiver=b.username where sendman="%s" order by id desc' % username)
        cursor.execute('select * from mailinfo where sendman="%s" order by mid desc' % username)
        list = cursor.fetchall()
        for l in list:
            allread = l[1].split(';')
            len = allread.__len__()
            unread = cursor.execute('select id from mail where readed ="n" and mid="%s"'% l[0])
            readedlist.append([len-1,int(unread)])

    except:
        pass


    cursor.close()
    conn.close()
    return render_template('sended.html',nickname=nickname,list=list,readedlist=readedlist)


@app.route('/mail_display/<id>')
def mail_display(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    nickname = session.get('nickname')
    username = session.get('username')
    row = []
    filename=[]
    filepath=[]
    try:
        cursor.execute('select * from mail where id="%s"' % id)
        row = cursor.fetchone()
        session['mailcontent'] = row[7]
        filename = row[5].split(';')
        filepath = row[6].split(';')
    except Exception:
        pass
    if row[1] == username:
        try:
            cursor.execute('update mail set readed = "y",readtime="%s" where id ="%s"' % (date,id))
        except Exception:
            pass
    else:
        pass

    conn.commit()
    cursor.close()
    conn.close()
    return render_template('mail_display.html',nickname=nickname,row=row,filename=filename,filepath=filepath)

@app.route('/sendmail_display/<id>')
def sendmail_display(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    nickname = session.get('nickname')
    username = session.get('username')
    row = []
    filename=[]
    filepath=[]
    readlist=[]
    state = 'sendmail'
    try:
        cursor.execute('select * from mailinfo where mid="%s"' % id)
        row = cursor.fetchone()
        session['mailcontent'] = row[7]
        filename = row[5].split(';')
        filepath = row[6].split(';')
    except Exception:
        pass
    if row[1] == username:
        try:
            cursor.execute('update mail set readed = "y" where id ="%s"' % id)
        except Exception:
            pass
    else:
        pass
    try:
        cursor.execute('select b.nickname,a.readed,a.readtime from mail a left join users b on a.receiver=b.username'
                       ' where mid="%s"' % id)
        readlist = cursor.fetchall()
    except:
        pass
    conn.commit()
    cursor.close()
    conn.close()
    return render_template('mail_display.html',nickname=nickname,row=row,filename=filename,filepath=filepath,
                           state=state,readlist=readlist)

@app.route('/mailcontent',methods=['get', 'post'])
def mailcontent():
    a = session.get('mailcontent')
    return a

@app.route('/reply/<id>',methods=['get', 'post'])
def reply(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    nickname = session.get('nickname')
    username = session.get('username')
    state = 'reply'
    row = []
    filename = []
    filepath = []
    try:
        cursor.execute('select * from mail where id ="%s" ' % id)
        row = cursor.fetchone()
        filename = row[5].split(';')
        filepath = row[6].split(';')
        session['mailfilename'] = row[5]
        session['mailfilepath'] = row[6]
    except:
        pass
    users = []
    try:
        cursor.execute('select * from users  order by ranking')
        users = cursor.fetchall()
    except:
        pass
    return render_template('sendmail.html',nickname=nickname,row=row,state=state,users=users,filename=filename,
                           filepath=filepath)

@app.route('/forward/<id>',methods=['get', 'post'])
def forward(id):
    if 'username' in session:
        pass
    else:
        return redirect('/')
    conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="18813091223", db="nnhyzx", charset="utf8")
    cursor = conn.cursor()
    date = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))  # 获取当前时间
    nickname = session.get('nickname')
    username = session.get('username')
    state = 'forward'
    row = []
    filename = []
    filepath = []
    try:
        cursor.execute('select * from mail where id ="%s" ' % id)
        row = cursor.fetchone()
        filename = row[5].split(';')
        filepath = row[6].split(';')
        session['mailfilename'] = row[5]
        session['mailfilepath'] = row[6]
    except:
        pass
    users = []
    try:
        cursor.execute('select * from users  order by ranking')
        users = cursor.fetchall()
    except:
        pass
    return render_template('sendmail.html',nickname=nickname,row=row,state=state,users=users,filename=filename,
                           filepath=filepath)

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5001)
